query to get assignment status in oracle fusion

• 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 employee person and assignment info

• Post category: BI Publisher
• Post comments: 0 Comments
• Post last modified: June 12, 2020
• Reading time: 12 mins read

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.

If the integration has to be incremental file, then the approach would be create a changes only HCM extract and achieve the requirement.

Have a look at the below posts to know more on HCM Extracts :

• Basics of HCM Extracts in Fusion HCM
• Resolve A system error occurred during processing in Extracts
• Configure GMFZT logging for an HCM Extract
• New features in HCM Extracts in 19B release
• Importing Changes Only HCM Extract post 20A update
• Scheduling and Cancelling an existing schedule of HCM Extract
• Skip Output file of HCM Extract when no data is fetched
• Basics of Changes Only Functionality in HCM Extract
• Using Table based Valueset when DBI is unavailable
• Most frequently used User Entities in HCM Extracts
• How to view the output of HCM Extract run by other users?
• How to link/connect the User Entities in HCM Extracts
• Fix for assertion failure error in HCM Extracts
• Top 50 Interview questions and answers on HCM Extracts
• HCM Extracts Dynamic Output Filename guide
• SQL Query to find scheduled HCM Extracts
• Achieve complex requirements using Custom Global Reports Data Model
• Payroll Flow and HCM Extracts Mapping for Payroll Module
• Hiding XML nodes in HCM Extract Output
• Running the latest extract version using Payroll Flow
• How to convert HCM Extract Export XML to Readable format?
• Deleted Data Report using Audit Functionality
• Passing Logged in User details to HCM Extract
• How to handle FF not compiled error for HCM Extracts
• How to handle Daylight Savings timings for HCM Extract schedules

If the integration requires a full file every time and if the vendor handles the changes based upon the file then we can go with creating a BI Report .

SQL Query to get the Person Demographic Information in BI Report :

Tip: The above long SQL Query is fetching data from around 33 tables and most of them are left outer joins, so even if they don’t have data you will still get data in the output.

You Might Also Like

SQL Query to get the modified fields from the Assignment table using Audit functionality

How to download the Payslip XML?

Deep Links and their usage in BI/OTBI 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.

Oracle Analytics Cloud and Server

• All Categories
• 12.3K Forums
• User Groups
• 1 Announcements
• Find Partners
• For Partners
• Hall of Fame: Spotlights
• Hall of Fame: Monthly Highlights
• Hall of Fame: Leaderboard

HCM | SQL query to get details of the employees belonging to departments below the selected departme

Requirement :

1) Report should display employees belonging to the departments of the selected organization type. 2) Report should also display employees belonging to departments below the selected departments as per organization hierarchy.

Scenario : Suppose the department of the employee is DEPARTMENT4.3 then the report should fetch the data of the employee whose department is DEPARTMENT4.3 along with the employees who belong to  DEPARTMENT4.3.1,  DEPARTMENT4.3.2,  DEPARTMENT4.3.3,  DEPARTMENT4.3.3.1.

Organization tree hierarchy :

NODE1:LEGAL EMPLOYER(ROOT)

NODE2: DEPARTMENT1

NODE3:          DEPARTMENT1.1

NODE4:                          DEPARTMENT1.1.1

                                      DEPARTMENT1.1.2

NODE3:           DEPARTMENT1.2

NODE2: DEPARTMENT2

NODE2: DEPARTMENT3

NODE3:           DEPARTMENT3.1

                        DEPARTMENT3.2

NODE2: DEPARTMENT4

NODE3:           DEPARTMENT4.1

                        DEPARTMENT4.2

                        DEPARTMENT4.3

NODE4:                         DEPARTMENT4.3.1

                                      DEPARTMENT4.3.2

                                      DEPARTMENT4.3.3

NODE5:                                          DEPARTMENT4.3.3.1

PS : I am able to achieve the first requirement by using attached SQL code but I need help to achieve the second requirement. I am not able to get any lead how should I accomplish the second requirement like does it require code modification or can be handled in template( currently using RTF template of excel type).

Code Snippet

• Oracle Fusion Analytics

Hi @Arti P , did you get a solution for this?

Dear All, Pls give me the complete (employee/person/worker) query to get all the details for these employees load through HDL worker.dat (worker,person name,person email,work relationship,work terms,assignments) please help me. thank you

Hi @User_Z86UI ,

Seems this is related to HCM product. Actually this forum is for Oracle Analytics Cloud and Server product, so please post your question/request in the proper forum. Try below forum and see if that helps.

https://community.oracle.com/customerconnect/categories/hcm-reporting-and-analytics-for-hcm

User Status Query – Oracle Fusion

The below query extracts the list of employee users along with their user status and employee status

Start typing and press Enter to search

Assignment Table in Oracle Fusion HCM

In Oracle Fusion HCM, the “Assignment” table is a key data table that stores information about an employee’s specific job or position within the organization. The Assignment table is a fundamental component of the Core HR module and holds critical data related to an employee’s work assignment, including job details, compensation, work schedule, and other employment-related information.

Here are some of the key fields typically found in the Assignment table in Oracle Fusion HCM:

• Assignment Number:  A unique identifier assigned to each employee’s work assignment within the organization.
• Person Number:  A unique identifier assigned to each employee within the system.
• Effective Start Date/Effective End Date:  The date range during which the assignment is valid. The Effective Start Date marks the beginning of the assignment, and the Effective End Date (if applicable) indicates the end date of the assignment.
• Job:  The job role or position title associated with the employee’s assignment.
• Position:  The specific organizational position or job position associated with the employee’s assignment.
• Employment Category:  The category of employment for the employee, such as regular, contingent, temporary, intern, etc.
• Worker Type:  The type of worker classification, such as full-time, part-time, contractor, etc.
• Pay Basis:  The basis on which the employee is compensated, such as hourly, salaried, or commission-based.
• Salary:  The employee’s salary or compensation amount associated with the assignment.
• Work Schedule:  The standard work hours or schedule for the employee’s assignment.
• Location:  The physical location or work site where the employee is assigned.
• Manager:  The manager or supervisor responsible for overseeing the employee’s work.
• Business Unit:  The organizational business unit to which the employee is assigned.
• Department:  The department or division within the organization to which the employee belongs.
• Assignment Status:  The current employment status of the employee’s assignment, such as active, suspended, terminated, etc.

The Assignment table serves as the foundation for managing and tracking employee assignments and employment-related data in Oracle Fusion HCM. It enables HR administrators to efficiently manage the workforce, maintain accurate records, and perform various HR-related actions, such as hiring, promotions, transfers, and terminations. Additionally, the Assignment table is closely integrated with other modules in Oracle Fusion HCM, such as Payroll, Benefits, and Absence Management, to ensure consistent and seamless HR processes throughout the organization.

Oracle Fusion HCM Training Demo Day 1 Video:

Conclusion:.

Unogeeks is the No.1 Training Institute for Fusion HCM Training. Anyone Disagree? Please drop in a comment

You can check out our Oracle Fusion HCM Training details here Oracle Fusion HCM Training

You can check out our other latest blogs on Oracle Fusion HCM Training in this Oracle Fusion HCM Blogs

———————————-

For Training inquiries:

Call/Whatsapp: +91 73960 33555

Mail us at: [email protected]

Our Website ➜  https://unogeeks.com

Instagram:  https://www.instagram.com/unogeeks

Facebook:  https://www.facebook.com/UnogeeksSoftwareTrainingInstitute

Twitter:  https://twitter.com/unogeeks

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

Oracle Application's Blog

The purpose of this blog , is to do Knowledge sharing with my Followers through my own Experiences. Contact us for Oracle Applications Job Support , Project Support and any other freelancing work.

• Fusion E-Books
• Fusion Technical
• Fusion Financial

Wednesday 3 April 2019

Oracle fusion user roles query: sql query to extract the oracle cloud user and roles information's, oracle fusion user roles query : sql query to extract the oracle cloud user and roles information's, 4 important tables of oracle fusion user roles query.

Detail SQL Query to Extract Oracle Fusion User & Roles

Select a1 . username , a1 . active_flag , a1 . credentials_email_sent , a2 . start_date , user_role_id , role_id , role_guid , abstract_role, job_role , data_role , role_common_name from   per_users a1 , per_user_roles a2 , per_roles_dn a3 where a1 . user_id = a2 . user_id and a2 . role_id = a3 . role_id and a2 . role_guid = a3 . role_guid   query 2:-   select prdt . role_id , prdt . role_name ,               prdt . description roledescription ,               prdt . source_lang from per_roles_dn_tl prdt query 3:- select pu.user_id,          pu.username,          ppnf.full_name,          prdt.role_id,          prdt.role_name,          prd.role_common_name,          prdt.description,          to_char (pur.start_date, 'dd-mon-yyyy') role_start_date,          to_char (pur.end_date, 'dd-mon-yyyy') role_end_date,          prd.abstract_role,          prd.job_role,          prd.data_role,          prd.duty_role,          prd.active_flag     from per_user_roles    pur,          per_users         pu,          per_roles_dn_tl   prdt,          per_roles_dn      prd,          per_person_names_f ppnf    where     1 = 1          and pu.user_id = pur.user_id          and prdt.role_id = pur.role_id          and prdt.language = userenv ('lang')          and prdt.role_id = prd.role_id          and nvl (pu.suspended, 'n') = 'n'    and pu.username =:p_username          and ppnf.person_id = pu.person_id          and ppnf.name_type = 'global'          and pu.active_flag = 'y'          and nvl (pu.start_date, sysdate) <= sysdate          and nvl (pu.end_date, sysdate) >= sysdate order by pu.username, prdt.role_name;, 5 comments:.

Magnificent blog I visit this blog it's extremely wonderful. Interestingly, in this blog content composed plainly and reasonable. The substance of data is useful. Oracle Fusion HCM Online Training Oracle Fusion SCM Online Training Oracle Fusion Financials Online Training Big Data and Hadoop Training In Hyderabad Oracle Fusion HCM Training In Hyderabad

Awesome post. your article is really informative and helpful for me and other bloggers too Oracle Fusion SCM Online Training

You can run this query using https://pi-cu.be

https://pi-cu.be/

Post a Comment

Contact us for any collaboration, project support & on job support work.

Know More About Us

• Products & Offerings

Learn Fusion Financials' (Online Book)

Oracle Fusion Self Paced Trainings in Discounted Prices

Buy Oracle Cloud Instance Access (Click to Know More)

Oracle Fusion/EBS Interview's Question Answer Series

• Top 33 Oracle Fusion Order Management Interview Questions
• TOP 31 Oracle Fusion SCM Cloud Interview Questions
• TOP 21 Oracle Fusion HCM Interview Questions
• Complete Tax Setups in Oracle Fusion
• Top 50 oracle reports interview questions
• 33 Most Important Oracle Fusion fixed assets interview questions
• TOP 33 Most Important Oracle Apps Technical Interview Questions
• 25 Most Important Oracle receivable interview questions
• 21 Most Important Oracle r12 Payables Interview Questions
• TOP 20 general ledger interview questions in oracle apps r12
• TOP 23 Most Important Oracle Fusion Interview Questions
• TOP 23 Most Important oaf interview questions

Online Oracle Courses (Free of Cost)

• Learn Tax Implementation in Oracle Fusion
• Learn P2P Cycle in Oracle Fusion
• Learn Oracle Reports XML Tutorial
• Learn Oracle Fusion Custom Roles Tutorial
• Learn Oracle Fusion Technical Online
• Learn Oracle Fusion/cloud Financials Online
• Learn Oracle Cloud BPM Online
• Learn Complete OAF Step by Step Online
• Learn Oracle Workflow Builder Online

Online Fusion Application Composer/Customization Book

Oracle Fusion Technical Book

Oracle Fusion Web Services(REST& SOAP API's) / Integration Book

Most Viewed Posts

• REST API for Oracle Fusion Cloud HCM
• List of Values
• Assignment Status Types List of Values

Get a status type

/hcmRestApi/resources/11.13.18.05/assignmentStatusTypesLov/{AssignmentStatusTypeId}

• AssignmentStatusTypeId(required): integer(int64) Unique identifier for the assignment status.
• dependency: string This parameter specifies dependencies which are fields that are set before and rolled back after generating the response. Generally they are used to preview the effects of an attribute change. The fields specified in this parameter are always set in the resource instance in question. When a child resource collection is requested and the parameter is set, the fields will be set in the parent resource instance before generating the resource collection payload. The value of this query parameter is a set of dependency fields. Example: dependency=ProductId=2 Format: <attr1>=<val1>,<attr2>=<value2>
• expand: string When this parameter is provided, the specified children are included in the resource payload (instead of just a link). The value of this query parameter is "all" or "". More than one child can be specified using comma as a separator. Example: ?expand=Employees,Localizations. Nested children can also be provided following the format "Child.NestedChild" (Example: ?expand=Employees.Managers). If a nested child is provided (Example: Employees.Managers), the missing children will be processed implicitly. For example, "?expand=Employees.Managers" is the same as "?expand=Employees,Employees.Managers" (which will expand Employees and Managers).
• fields: string This parameter filters the resource fields. Only the specified fields are returned, which means that if no fields are specified, no fields are returned (useful to get only the links). If an indirect child resource is provided (Example: Employees.Managers), the missing children will be processed implicitly. For example, "?fields=Employees.Managers:Empname" is the same as "?fields=;Employees:;Employees.Managers:Empname" (which will only return the "Empname" field for Managers). the value of this query parameter is a list of resource fields. The attribute can be a direct (Example: Employees) or indirect (Example: Employees.Managers) child. It cannot be combined with expand query parameter. If both are provided, only fields will be considered. Format: ?fields=Attribute1,Attribute2 Format for fields in child resource: ?fields=Accessor1:Attribute1,Attribute2
• links: string This parameter can be used to show only certain links while accessing a singular resource or a resource collection. The parameter value format is a comma-separated list of : <link_relation> Example: self,canonical
• onlyData: boolean The resource item payload will be filtered in order to contain only data (no links section, for example).
• Metadata-Context: If the REST API supports runtime customizations, the shape of the service may change during runtime. The REST client may isolate itself from these changes or choose to interact with the latest version of the API by specifying this header. For example: Metadata-Context:sandbox="TrackEmployeeFeature".
• REST-Framework-Version: The protocol version between a REST client and service. If the client does not specify this header in the request the server will pick a default version for the API.

There's no request body for this operation.

• application/json

Default Response

• Metadata-Context(required): If the REST API supports runtime customizations, the shape of the service may change during runtime. The REST client may isolate itself from these changes or choose to interact with the latest version of the API by specifying this header. For example: Metadata-Context:sandbox="TrackEmployeeFeature".
• REST-Framework-Version(required): The protocol version between a REST client and service. If the client does not specify this header in the request the server will pick a default version for the API.
• AssignmentStatusCode: string Maximum Length: 30 Unique code representing the assignment status.
• AssignmentStatusTypeId: integer (int64) Unique identifier for the assignment status.
• EndDate: string (date) Date after when the assignment status is not active.
• HRStatus: string Title: HR System Status Maximum Length: 30 HR status used within the application to determine how the assignment is processed.
• links: array Links Title: Links The link relations associated with the resource instance.
• PayStatus: string Title: Pay System Status Maximum Length: 30 Payroll status indicating whether the assignment is processed in payroll runs.
• StartDate: string (date) Date from when the assignment status is active.
• UserStatus: string Title: User Status Maximum Length: 80 Descriptive name of the assignment status.
• Array of: object link
• href: string Title: hyperlink reference The URI to the related resource.
• kind: string Title: kind Allowed Values: [ "collection", "item", "describe", "other" ] The kind of the related resource.
• name: string Title: name The name of the link to the related resource.
• properties: object properties
• rel: string Title: relation Allowed Values: [ "self", "lov", "parent", "canonical", "child", "enclosure", "action", "custom" ] The name of the relation to the resource instance. Example: self.
• changeIndicator: string Change indicator or the ETag value of the resource instance.