oracle hrms assignment history query

To view an employee's assignment history:

Rajesh Apps Blog

Friday 5 december 2014, date track history change field summary query in oracle hrms.

15 comments:

Rajesh, I don't think we need this big query to fetch this information...

Hi Subha, Please paste the simplified query here which will be useful for me as well as the users. I can update the post if it is useful. Thanks. Rajesh

Can you please suggest what is to be changed? @ Rajesh

Hi, Could u check the views ending with _D. PER_ALL_ASSIGNMENTS_F is the date tracked table. PER_ALL_ASSIGNMENTS_D date tracked view, which contains the history. Regards Kalyana Chakravarthy

Hi Rajesh, I tried with your query but it is not fetching any data for me

Hi Rajesh, My Apologies, its working fine. Thanks for the post

Just wondering if anyone has a similar query for time card hxt changes?

Good Query. Thanks for posting

Is there any query for fetching the previous department, previous location,previous position and their manager. Regards, Vignesh

HI Rajesh, I need date tracking history in people window do you have please guide

thanks for sharing a nice information. keep it up great work. Best HR software in UAE Cloud based HR software in UAE Best HR software in Abu Dhabi Human Resource and Payroll Software in UAE

Not fetching any data.

Nice article thank you Rajesh

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

Friday 31 January 2020

Employee assignment query in oracle apps.

Important Tables of Employee assignment query in oracle apps

Detail employee assignment query in oracle apps, 0 comments:, 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

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

• 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

How to extract the list of Alerts from Alert Composer?

How to fetch the Employee Social Insurance Calculation Card details for AE legislation?

SQL Query to fetch Costing of Payroll information

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 Apps Knowledge Sharing

Meant for sharing our knowledge related to Oracle E-Business Suite

• TECHNICAL TUTORIALS
• USEFUL LINKS
• ARTICLE INDEX
• VIDEO INDEX

Saturday, August 1, 2020

• Query to Fetch Employee Details in Oracle Apps R12 (PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, PER_JOBS, PER_ALL_POSITIONS, PER_PHONES)

Below query will help you to get basic employee details and joins between key employee tables.

Tables Involved:

• per_all_people_f
• per_all_assignments_f
• gl_code_combinations_kfv
• per_all_positions
• hr_locations_all

SELECT ( SELECT name FROM hr_all_organization_units WHERE organization_id = paaf . business_group_id ) business_group , papf1 . employee_number , papf1 . first_name , papf1 . last_name , papf1 . full_name , papf1 . sex gender , papf1 . effective_start_date , papf1 . effective_end_date , papf1 . email_address , ( SELECT hloc . location_code FROM hr_locations_all hloc WHERE hloc . location_id = paaf . location_id ) employee_location , papf2 . employee_number supervisor_employee_number , papf2 . full_name supervisor_name , ( SELECT pp . phone_number FROM per_phones pp WHERE papf1 . person_id = pp . parent_id AND pp . parent_table = 'PER_ALL_PEOPLE_F' AND pp . date_from = papf1 . effective_start_date AND NVL ( pp . date_to , papf1 . effective_end_date ) = papf1 . effective_end_date ) work_telephone_number , ( SELECT name FROM per_jobs WHERE job_id = paaf . job_id ) job_name , ( SELECT name FROM per_all_positions WHERE position_id = paaf . position_id ) position_name , ( SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id = paaf . default_code_comb_id ) default_charge_account , ( SELECT name FROM gl_ledgers WHERE ledger_id = paaf . set_of_books_id ) ledger_name FROM per_all_people_f papf1 , per_all_people_f papf2 , per_all_assignments_f paaf WHERE papf1 . person_id ( + ) = paaf . person_id AND papf2 . person_id ( + ) = paaf . supervisor_id AND paaf . primary_flag = 'Y' AND papf1 . current_employee_flag = 'Y' AND papf2 . current_employee_flag = 'Y'

Do you think this Article is useful?

0 Responses to “Query to Fetch Employee Details in Oracle Apps R12 (PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, PER_JOBS, PER_ALL_POSITIONS, PER_PHONES)”

Post a comment, stay connected.

Popular Posts

• Query to get basic AP Invoice and Payment information from Oracle Apps R12
• Query to fetch Purchase Receipt and its related Purchase order, Vendor details in Oracle Apps R12 (RCV_TRANSACTIONS, RCV_SHIPMENT_HEADERS, RCV_SHIPMENT_LINES)
• WSH_DELIVERY_DETAILS.RELEASED_STATUS ( Pick Release Status)
• Query to get active Customer and their Profile Details (HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUSTOMER_PROFILES, HZ_CUST_PROFILE_AMTS)
• How to find Order types in R12 (Script for Order types)
• How to get Oracle Application User Password from database - oracle.apps.fnd.security.WebSessionManagerProc.decrypt
• Query To Fetch Customer Account/Site Contact Details in R12
• Query to fetch Purchase Requisition Approval Action History Details in Oracle Apps R12 (PO_ACTION_HISTORY)
• Query to fetch Legal Entity, Business group details in Oracle Apps R12 - per_business_groups,xle_firstparty_information_v

Blog Archive

shareoracleapps.com

Site Stats (Last 30 Days)

• Install App

Analytics Software

For appeals, questions and feedback about Oracle Forums, please email [email protected] . Please ask technical questions in the appropriate category. Thank you!

Date Track history tables in Oracle HRMS & Discoverer

OraclePort.com

Oracle Techno Functional Portal, Technical and Functional Resource

Oracle HR and Payroll Related Queries

Oracle ebs/apps r12. ( oracle hr / payroll ) important queries, costing detail query:.

select paf.ASSIGNMENT_NUMBER, ppf.FULL_NAME ,pet.element_name,pca.CONCATENATED_SEGMENTS, decode(pc.DEBIT_OR_CREDIT,’D’,pc.COSTED_VALUE) Debit, decode(pc.DEBIT_OR_CREDIT,’C’,pc.COSTED_VALUE) Credit from per_people_f ppf, per_assignments_f paf, pay_assignment_actions pav, pay_payroll_actions ppa, pay_costs pc, PAY_COST_ALLOCATION_KEYFLEX pca, pay_element_types_f pet, pay_run_results prr, pay_run_result_values prrv where ppf.PERSON_ID=paf.PERSON_ID and paf.ASSIGNMENT_ID=pav.ASSIGNMENT_ID and paf.PRIMARY_FLAG=’Y’ and ppf.EMPLOYEE_NUMBER=:p_emp_no and pav.PAYROLL_ACTION_ID=ppa.PAYROLL_ACTION_ID and trunc(ppa.EFFECTIVE_DATE) between :p_start_date and :p_end_date and pav.ASSIGNMENT_ACTION_ID=pc.ASSIGNMENT_ACTION_ID and pc.COST_ALLOCATION_KEYFLEX_ID=pca.COST_ALLOCATION_KEYFLEX_ID and pet.ELEMENT_TYPE_ID=prr.ELEMENT_TYPE_ID and prr.RUN_RESULT_ID=prrv.RUN_RESULT_ID and pc.RUN_RESULT_ID=prrv.RUN_RESULT_ID and pc.INPUT_VALUE_ID=prrv.INPUT_VALUE_ID and :p_end_date between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE and ppf.EFFECTIVE_END_DATE = (select max(effective_end_date) from per_people_f where person_id=ppf.PERSON_ID) and paf.EFFECTIVE_END_DATE = (select max(effective_end_date) from per_assignments_f where assignment_id=paf.ASSIGNMENT_ID)

Query to Find of Number of Working Days in Months.

SELECT themonth, count(theday) cnt FROM ( SELECT TO_CHAR(TRUNC(SYSDATE,’YY’)+LEVEL-1,’Month’) themonth , TO_CHAR(TRUNC(SYSDATE,’YY’)+LEVEL-1,’DY’) theday FROM dual CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE,’YY’),12)-TRUNC(SYSDATE,’YY’) ) WHERE theday NOT IN (‘SAT’,’SUN’) GROUP BY themonth ORDER BY TO_DATE(themonth,’MM’)

Query to to find the Employee Supervisor name

SELECT papf1.full_name supervisor_nameFrom apps.per_all_people_f papf,apps.per_all_assignments_f paaf,apps.per_all_people_f papf1WHERE papf.person_id = paaf.person_idAND paaf.primary_flag = ‘Y’AND paaf.assignment_type = ‘E’AND paaf.supervisor_id = papf1.person_idAND papf1.current_employee_flag = ‘Y’AND papf.business_group_id = paaf.business_group_idAND SYSDATE BETWEEN papf.effective_start_date and papf.effective_end_dateAND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_dateAND SYSDATE BETWEEN papf1.effective_start_date AND papf1.effective_end_dateAND papf.employee_number = :p_emp_number;

Query to find out payroll Costing Detail  /  “How to find out payroll costed accounts” / “Query to find out Payroll Charged Accounts”.

SELECT distinct d.EMPLOYEE_NUMBER,d.FULL_NAME,a.CONCATENATED_SEGMENTS, a.element_name,decode(a.debit_or_credit,’Debit’,COSTED_VALUE,”) Debit  , decode(debit_or_credit,’Credit’,COSTED_VALUE,”) Credit FROM  pay_costs_v a, pay_assignment_actions_v b,per_assignments_x c,per_people_x d where     d.EMPLOYEE_NUMBER= nvl(:p_emp_no,d.EMPLOYEE_NUMBER) and   trunc (b.EFFECTIVE_DATE)  between :p_from_date and :p_to_date and a.ASSIGNMENT_ACTION_ID=b.ASSIGNMENT_ACTION_ID and  b.ASSIGNMENT_ID=c.ASSIGNMENT_ID and c.PERSON_ID=d.PERSON_ID

Query to Find All Active Employees and Current Salary.

select EMPLOYEE_NUMBER,a.FULL_NAME,x.USER_PERSON_TYPE, c.PROPOSED_SALARY_n Basi_Salary–,c.CHANGE_DATE–,c.* from per_people_f A,per_assignments_f b,pER_PAY_pROposals c ,per_person_types x , per_person_type_usages_f e where a.PERSON_ID=b.PERSON_ID and b.ASSIGNMENT_ID=C.ASSIGNMENT_ID and a.EMPLOYEE_NUMBER is not null –and a.EMPLOYEE_NUMBER=:emp_num and c.CHANGE_DATE = (select max(d.CHANGE_DATE) from pER_PAY_pROposals d where d.ASSIGNMENT_ID=b.ASSIGNMENT_ID and d.approved = ‘Y’) –and c.CHANGE_DATE>=:change_date and b.PAYROLL_ID=62 and :p_effective_date between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE and :p_effective_date between b.EFFECTIVE_START_DATE and b.EFFECTIVE_END_DATE and a.PERSOn_id = e.PERSON_ID and a.EFFECTIVE_START_DATE between e.EFFECTIVE_START_DATE and e.EFFECTIVE_END_DATE and e.PERSON_TYPE_ID = x.PERSON_TYPE_ID and x.SYSTEM_PERSON_TYPE = ‘EMP’

Query fo Find out all Earning and Deduction Elements and values after Payroll Run

SELECT ppf.employee_number,ppf.person_id,ppf.full_name,ppa.TIME_PERIOD_ID,ppa.EFFECTIVE_DATE,TP.PERIOD_NAME,paf.ORGANIZATION_ID, sum(decode(pec.CLASSIFICATION_NAME,’Earnings’,to_number(rrv.result_value), 0) ) Earnings, sum(decode(pec.CLASSIFICATION_NAME,’Voluntary Deductions’,to_number(rrv.result_value), ‘Involuntary Deductions’,to_number(rrv.result_value), ‘Employer Charges’,to_number(rrv.result_value), 0) ) Deductions –ety.element_name,ety.CLASSIFICATION_ID — PD.SEGMENT5  POSITION_NO,PD.SEGMENT6 POSITION_NAME, FROM per_people_x ppf, per_assignments_x paf, pay_assignment_actions pas, pay_payroll_actions ppa, pay_run_results rr, pay_run_result_values rrv, pay_element_types_f ety, pay_input_values_F I, PER_TIME_PERIODS TP, PAY_ELEMENT_CLASSIFICATIONS_VL pec WHERE ppf.person_id = paf.person_id AND paf.assignment_id = pas.assignment_id AND pas.assignment_action_id = rr.assignment_action_id AND ppa.payroll_action_id = pas.payroll_action_id AND rr.element_type_id = ety.element_type_id AND i.element_type_id = ety.element_type_id AND rrv.run_result_id = rr.run_result_id AND rrv.input_value_id = i.input_value_id and  TP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID and ety.CLASSIFICATION_ID=pec.CLASSIFICATION_ID AND i.name = ‘Pay Value’ —   AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PAF.POSITION_ID) = PD.POSITION_DEFINITION_ID and ppa.EFFECTIVE_DATE  BETWEEN    :p_st_effect_date  AND  :p_end_effect_date and ppf.employee_number  = nvl(:p_emp_number,ppf.employee_number) group by  ppf.full_name,ppa.TIME_PERIOD_ID,effective_date,–To_Number(Wassa_HR_PACKAGE.Nid_Salary_By_Date(paf.assignment_id,ppa.EFFECTIVE_DATE,’N’)), ppf.employee_number,ppf.person_id ,–PD.SEGMENT5 ,PD.SEGMENT6, TP.period_name,paf.ORGANIZATION_ID

Query for Oracle Payroll Salary Slip /  Salary Slip Detail Report Query / How to find out detail of salary slip/ Element wise Landscape Salary Slip at Payroll Run Levle  or quick pay level

Note:  I have hardcoded element names. You can also hardcode your elements to get Landscaped salary slip.

SELECT ppf.employee_number,ppf.person_id,ppf.full_name,ppa.TIME_PERIOD_ID,ppa.EFFECTIVE_DATE  ,TP.PERIOD_NAME, sum(decode(ety.element_name,’Basic Salary’                         ,TO_NUMBER(rrv.result_value), ‘Basic Sick Leave Payment’             ,TO_NUMBER(rrv.result_value), ‘Basic Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value), 0)) Earned_salary, /********************************************************************************************************/ sum(decode(ety.element_name,’Transportation Allowance’                      ,TO_NUMBER(rrv.result_value), ‘Transportation Sick Leave Payment’             ,TO_NUMBER(rrv.result_value), ‘Transportation Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value), 0)) Transportation_allowance, /*************************************************************************************************************/ sum(decode(ety.element_name,’Work Type Allowance’                      ,TO_NUMBER(rrv.result_value), ‘Work Type Sick Leave Payment’             ,TO_NUMBER(rrv.result_value), ‘Work Type Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value), 0)) worktype_allowance, /***************************************************************************************************************/ sum(decode(ety.element_name,’Damages Allowance’                      ,TO_NUMBER(rrv.result_value), ‘Damages Sick Leave Payment’             ,TO_NUMBER(rrv.result_value), ‘Damages Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value), 0)) Damage_allowance, /*****************************************************************************************************************/ sum(decode(ety.element_name,’Danger Allowance’                      ,TO_NUMBER(rrv.result_value), ‘Danger Sick Leave Payment’             ,TO_NUMBER(rrv.result_value), ‘Danger Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value), 0)) Danger_allowance, /*************************************************************************************************************/ sum(decode(ety.element_name,’Inflation Allowance’    ,TO_NUMBER(rrv.result_value),0)) Inflation_Allowance, /*********************************************************  Deductions  ************************************************/ sum(decode(ety.element_name,’Loan Recovery’         ,TO_NUMBER(rrv.result_value),0)) Loan_recovery, sum(decode(ety.element_name,’Loan Recovery Housing’ ,TO_NUMBER(rrv.result_value),0)) Loan_Recovery_Housing, sum(decode(ety.element_name,’Loan Recovery Others’  ,TO_NUMBER(rrv.result_value),0)) Loan_Recovery_Others, sum(decode(ety.element_name,’Housing Deduction’     ,TO_NUMBER(rrv.result_value),0)) Housing_Deduction, sum(decode(ety.element_name,’Penalty’               ,TO_NUMBER(rrv.result_value),0)) Penalty, /***********************************************************************************************************************/ sum(decode(ety.element_name,’Civil Pension’                      ,TO_NUMBER(rrv.result_value), ‘Social Insurance’             ,TO_NUMBER(rrv.result_value), 0)) Civil_pension, /************************************************************************************************************************************/ sum(decode(ety.element_name,’In Out Leave Deduction’ ,TO_NUMBER(rrv.result_value),0)) In_Out_Leave_Deduction, sum(decode(ety.element_name,’Unpaid Leave Deduction’ ,TO_NUMBER(rrv.result_value),0)) Unpaid_Leave_Deduction, sum(decode(ety.element_name,’Retrieve Mony Recovery’ ,TO_NUMBER(rrv.result_value),0)) Retrieve_Mony_Recovery FROM per_people_x ppf, per_assignments_x paf, pay_assignment_actions pas , pay_payroll_actions ppa, pay_run_results rr, pay_run_result_values rrv, pay_element_types_f ety, pay_input_values_F I , PER_TIME_PERIODS TP —     PER_POSITION_DEFINITIONS PD — PAY_INPUT_VALUES_F WHERE ppf.person_id = paf.person_id AND paf.assignment_id = pas.assignment_id AND pas.assignment_action_id = rr.assignment_action_id AND ppa.payroll_action_id = pas.payroll_action_id AND rr.element_type_id = ety.element_type_id AND i.element_type_id = ety.element_type_id AND rrv.run_result_id = rr.run_result_id AND rrv.input_value_id = i.input_value_id and  TP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID AND i.name = ‘Pay Value’ —   AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PAF.POSITION_ID) = PD.POSITION_DEFINITION_ID and ppa.EFFECTIVE_DATE  BETWEEN    :P_FROM_DATE      AND  :P_TO_DATE and ppf.employee_number    = :P_Employee_number –in(34000/*1546014859,14666,35343,15201,15202*/) group by  ppf.full_name,ppa.TIME_PERIOD_ID,effective_date, ppf.employee_number,ppf.person_id ,–PD.SEGMENT5 ,PD.SEGMENT6, TP.period_name order  by  ppa.EFFECTIVE_DATE

19 thoughts on “ Oracle HR and Payroll Related Queries ”

I visited your web site, you did a real y great a great job the queries & other material regarding oracle is very helpful.

Najeebullah

HI I visited your web site and the queries what are posted are really excellent and very helpful to me thank you very much

Excellent work Dude. Thank you.

Hi, When Payroll Rollback runs, then what changes required to above queries? Is it like, when I run payroll again then, one more record will get inserted for same element in the same period. But we have one more record with same data. How to handle this scenario?

Rollback simply delete records from database. No Need to change any thing. Just Rerun Payroll/prepayment etc and execute query.

your blog gives me a lot of information and helped a lot. sir i have a problem of duplication in my report the query of the report as follows /*query*/

select DISTINCT ppx.employee_number, ppx.person_id, nvl(UPDATED_BY(ppx.person_id,PAAF.last_updated_by,EMP_STATUS(ppx.person_id,NVL(ppos.actual_termination_date,paaf.EFFECTIVE_END_DATE)),paaf.EFFECTIVE_END_DATE),’Âáí’) UPDATED_BY_NAME, PHON_NUM(ppx.person_id) PHON_NUM, ppx.NATIONAL_IDENTIFIER, ppx.full_name, DECODE(ppx.SEX,’M’,’ÐßÑ’,’ÃäËì’) sex, ppd.segment1 pos_name, ppd.segment2 pos_nu, paaou.name pos_org_name, –DECODE(person_type_id,1120,’??? ??? ????’,’??E???’) employed , — TO_CHAR(paaf.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) ASS_CREATE_DATE, TO_CHAR(ppx.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) PER_CREATE_DATE, — TO_CHAR(pp.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) ADD_CREATE_DATE, trunc(months_between(sysdate,ppx.date_of_birth)/12) year, trunc(mod(months_between(sysdate,ppx.date_of_birth),12)) month, trunc(sysdate-Add_Months(ppx.date_of_birth,Trunc(months_between(sysdate,ppx.date_of_birth)/12)*12 +TRUNC(MOD(months_between(sysdate,ppx.date_of_birth),12)))) DAY,

trunc(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE)/12) ASS_year, trunc(mod(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE),12)) ASS_month, trunc(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE)-Add_Months(paaf.EFFECTIVE_START_DATE,Trunc(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE)/12)*12 +TRUNC(MOD(months_between(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),paaf.EFFECTIVE_START_DATE),12)))) ASS_DAY,

TO_CHAR(paaf.EFFECTIVE_START_DATE,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) ASS_START_DATE, TO_CHAR(DECODE(paaf.EFFECTIVE_END_DATE,to_date(’12/31/4712′,’MM/DD/YYYY’),sysdate,paaf.EFFECTIVE_END_DATE),’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) ASS_END_DATE,

ca.NAME Actual_ass,

pg.name Grade_name, hr_general.decode_STEP (paaf.special_ceiling_step_id ,SYSDATE) grade_STEP, TO_CHAR(ppx.START_DATE ,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) START_DATE, TO_CHAR(ppx.date_of_birth ,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) birth_date, substr(ppg.group_name,1,instr(ppg.group_name,’.’)-1) p_group, fcl.MEANING NATIONALITY, ppx.EMAIL_ADDRESS, pca.name emoloyee_curr_org, pqtv.name degree, pq.title, pq.grade_attained, pq.awarding_body, ppx.TOWN_OF_BIRTH, DECODE(ppx.MARITAL_STATUS,’M’,’ãÊÒæÌ’,’S’,’ÃÚÒÈ’) Mat_status, rg.MEANING hired_as,

–TO_CHAR(pp.phone_number) phone_number,

pq.comments grad_date, uni_pos.MEANING uni_position, TO_CHAR(ppos.actual_termination_date ,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) leave_date, leave_reas.MEANING leave_reason, ppp.proposed_salary_n from per_all_assignments_f paaf, per_people_x ppx,

PAY_PEOPLE_GROUPS ppg, per_grades pg, per_position_definitions ppd, HR_ALL_POSITIONS_F hapf, per_all_organization_units paaou, PER_QUALIFICATIONS_V pq, fnd_common_lookups fcl, PER_QUALIFICATION_TYPES_V pqtv, per_collective_agreements pca , –per_phones pp , fnd_common_lookups rg, fnd_common_lookups uni_pos, Per_Periods_Of_Service ppos, fnd_common_lookups leave_reas, per_pay_proposals ppp, –OMAR –FOR ACCTUAL ORGANIZATION PER_COLLECTIVE_AGREEMENTS CA

where ppx.person_id = paaf.person_id and ppos.person_id = ppx.PERSON_ID and rg.LOOKUP_TYPE (+) = ‘REGISTERED_DISABLED’ and rg.LOOKUP_CODE (+) = ppx.REGISTERED_DISABLED_FLAG and uni_pos.LOOKUP_TYPE (+) = ‘EMP_CAT’ and uni_pos.LOOKUP_code (+) = paaf.employment_category and leave_reas.LOOKUP_TYPE (+) = ‘LEAV_REAS’ AND leave_reas.LOOKUP_CODE (+) = ppos.leaving_reason

— and pp.parent_id(+) = ppx.PERSON_ID

and pq.person_id(+) = ppx.person_id and pqtv.qualification_type_id(+) = pq.qualification_type_id and ppg.people_group_id = paaf.people_group_id and paaf.organization_id = paaou.organization_id and paaf.position_id = hapf.position_id (+) and hapf.position_definition_id = ppd.position_definition_id (+) and pg.grade_id(+) = paaf.grade_id and fcl.LOOKUP_TYPE = ‘NATIONALITY’ AND fcl.LOOKUP_CODE(+) = ppx.NATIONALITY and pca.collective_agreement_id(+) = paaf.collective_agreement_id and to_number(to_char(paaf.effective_end_date,’yyyy’)) > 4000 and ppp.assignment_id = paaf.assignment_id and ppp.change_date = (select max(ppp1.change_date) from per_pay_proposals ppp1 where ppp1.assignment_id = ppp.assignment_id) and ppx.person_type_id = 1120 — AND sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE — AND sysdate between ppx.EFFECTIVE_START_DATE and ppx.EFFECTIVE_END_DATE

–FOR ACCTUAL ORGANIZATION AND CA.COLLECTIVE_AGREEMENT_ID(+) = PAAF.COLLECTIVE_AGREEMENT_ID

union select distinct ppx.employee_number, ppx.person_id, nvl(UPDATED_BY(ppx.person_id,PAAF.last_updated_by,EMP_STATUS(ppx.person_id,NVL(ppos.actual_termination_date,paaf.EFFECTIVE_END_DATE)),paaf.EFFECTIVE_END_DATE),’Âáí’) UPDATED_BY_NAME, PHON_NUM(ppx.person_id) PHON_NUM, ppx.NATIONAL_IDENTIFIER , ppx.full_name,decode(ppx.SEX,’M’,’ÐßÑ’,’ÃäËì’) sex, ppd.segment1 pos_name, ppd.segment2 pos_nu,paaou.name pos_org_name, — decode(person_type_id,1120,’??? ??? ????’,’??E???’) employed ,

TO_CHAR(paaf.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) ASS_CREATE_DATE, TO_CHAR(ppx.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) PER_CREATE_DATE,

–TO_CHAR(pp.LAST_UPDATE_DATE,’RRRR-MM-DD HH:MM:SS’,’nls_calendar=”Arabic Hijrah”’) ADD_CREATE_DATE,

trunc(months_between(sysdate,ppx.date_of_birth)/12) B_year, trunc(mod(months_between(sysdate,ppx.date_of_birth),12)) B_month, trunc(sysdate-Add_Months(ppx.date_of_birth,Trunc(months_between(sysdate,ppx.date_of_birth)/12)*12 +TRUNC(MOD(months_between(sysdate,ppx.date_of_birth),12)))) B_DAY,

CA.name Actual_ass, pg.name Grade_name, hr_general.decode_STEP (paaf.special_ceiling_step_id ,SYSDATE) grade_STEP,

TO_CHAR(ppx.START_DATE ,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) START_DATE, TO_CHAR(ppx.date_of_birth ,’RRRR-MM-DD’,’nls_calendar=”Arabic Hijrah”’) birth_date, substr(ppg.group_name,1,instr(ppg.group_name,’.’)-1) p_group, fcl.MEANING NATIONALITY, ppx.EMAIL_ADDRESS, pca.name emoloyee_curr_org, pqtv.name degree, pq.title, pq.grade_attained, pq.awarding_body, ppx.TOWN_OF_BIRTH, decode(ppx.MARITAL_STATUS,’M’,’ãÊÒæÌ’,’S’,’ÃÚÒÈ’) Mat_status, rg.MEANING hired_as,

–pp.phone_number,

PAY_PEOPLE_GROUPS ppg, per_grades pg, per_position_definitions ppd, HR_ALL_POSITIONS_F hapf, per_all_organization_units paaou, PER_QUALIFICATIONS_V pq, fnd_common_lookups fcl, PER_QUALIFICATION_TYPES_V pqtv, per_collective_agreements pca ,

–per_phones pp ,

fnd_common_lookups rg, fnd_common_lookups uni_pos, Per_Periods_Of_Service ppos, fnd_common_lookups leave_reas, per_pay_proposals ppp, –OMAR –FOR ACCTUAL ORGANIZATION PER_COLLECTIVE_AGREEMENTS CA

–and pp.parent_id(+) = ppx.PERSON_ID and pq.person_id(+) = ppx.person_id and pqtv.qualification_type_id(+) = pq.qualification_type_id and ppg.people_group_id = paaf.people_group_id and paaf.organization_id = paaou.organization_id and paaf.position_id = hapf.position_id (+) and hapf.position_definition_id = ppd.position_definition_id (+) and pg.grade_id(+) = paaf.grade_id and fcl.LOOKUP_TYPE = ‘NATIONALITY’ AND fcl.LOOKUP_CODE(+) = ppx.NATIONALITY and pca.collective_agreement_id(+) = paaf.collective_agreement_id —- and to_number(to_char(paaf.effective_end_date,’yyyy’)) > 4000 and ppp.assignment_id = paaf.assignment_id and ppp.change_date = (select max(ppp1.change_date) from per_pay_proposals ppp1 where ppp1.assignment_id = ppp.assignment_id)

–FOR ACCTUAL ORGANIZATION AND CA.COLLECTIVE_AGREEMENT_ID(+) = PAAF.COLLECTIVE_AGREEMENT_ID and ppx.person_type_id = 1123 — AND sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE — AND sysdate between ppx.EFFECTIVE_START_DATE and ppx.EFFECTIVE_END_DATE

order by full_name

please help me where exactly a mistake is

Is your issue resolved?

no its showing duplicate rows for ex- employee information

send me result of following query

select * from per_all_assignments_f paaf, per_people_x ppx, PAY_PEOPLE_GROUPS ppg, per_grades pg, per_position_definitions ppd, HR_ALL_POSITIONS_F hapf, per_all_organization_units paaou, PER_QUALIFICATIONS_V pq, fnd_common_lookups fcl, PER_QUALIFICATION_TYPES_V pqtv, per_collective_agreements pca , –per_phones pp , fnd_common_lookups rg, fnd_common_lookups uni_pos, Per_Periods_Of_Service ppos, fnd_common_lookups leave_reas, per_pay_proposals ppp, –OMAR –FOR ACCTUAL ORGANIZATION PER_COLLECTIVE_AGREEMENTS CA

where ppx.person_id = paaf.person_id and ppos.person_id = ppx.PERSON_ID and rg.LOOKUP_TYPE (+) = ‘REGISTERED_DISABLED’ and rg.LOOKUP_CODE (+) = ppx.REGISTERED_DISABLED_FLAG and uni_pos.LOOKUP_TYPE (+) = ‘EMP_CAT’ and uni_pos.LOOKUP_code (+) = paaf.employment_category and leave_reas.LOOKUP_TYPE (+) = ‘LEAV_REAS’ AND leave_reas.LOOKUP_CODE (+) = ppos.leaving_reason –and pp.parent_id(+) = ppx.PERSON_ID and pq.person_id(+) = ppx.person_id and pqtv.qualification_type_id(+) = pq.qualification_type_id and ppg.people_group_id = paaf.people_group_id and paaf.organization_id = paaou.organization_id and paaf.position_id = hapf.position_id (+) and hapf.position_definition_id = ppd.position_definition_id (+) and pg.grade_id(+) = paaf.grade_id and fcl.LOOKUP_TYPE = ‘NATIONALITY’ AND fcl.LOOKUP_CODE(+) = ppx.NATIONALITY and pca.collective_agreement_id(+) = paaf.collective_agreement_id —- and to_number(to_char(paaf.effective_end_date,’yyyy’)) > 4000 and ppp.assignment_id = paaf.assignment_id and ppp.change_date = (select max(ppp1.change_date) from per_pay_proposals ppp1 where ppp1.assignment_id = ppp.assignment_id)

–FOR ACCTUAL ORGANIZATION AND CA.COLLECTIVE_AGREEMENT_ID(+) = PAAF.COLLECTIVE_AGREEMENT_ID and ppx.person_type_id = 1123 – AND sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE – AND sysdate between ppx.EFFECTIVE_START_DATE and ppx.EFFECTIVE_END_DATE

its giving non duplicate data fro single employee for giving employee parameter

but when i am removing the parameter then its displays only 16 rows instead of 1800+ rows should be displayed

sir i want to create a disc for monthly salary report by using payroll group. could you please provide information about how to create it .

best regards syed zubair

this is the report for which the disc to be prepared

SELECT ALL b.SESSION_ID, b.PERSON_ID, b.EMPLOYEE_NUMBER, b.FULL_NAME, b.GRADE, b.GRADE_STEP, b.GRADE_SEQ, b.JOB, b.POSITION, b.PAYROLL_ID, b.PAYROLL_NAME, b.PAID_PERIOD, b.PAYROLL_ACTION_ID, b.ELEMENT_TYPE_ID, b.ELEMENT_NAME, b.ELEMENT_VAL1, b.ELEMENT_VAL2, b.ELEMENT_VAL3, b.ELEMENT_VAL4, b.ELEMENT_VAL5, b.ELEMENT_VAL6, b.ELEMENT_VAL7, b.ELEMENT_VAL8, b.ELEMENT_VAL9, b.ELEMENT_VAL10, b.ELEMENT_VAL11, b.ELEMENT_VAL12, b.ELEMENT_VAL13, b.ELEMENT_VAL14, b.ELEMENT_VAL15, b.ELEMENT_VAL16, b.ELEMENT_VAL17, b.ELEMENT_VAL18, b.ELEMENT_VAL19, b.ELEMENT_VAL20, b.ELEMENT_VAL21, b.ELEMENT_VAL22, b.GROUP_SEG1, b.GROUP_SEG2,

nprv.ELEMENT_VAL1 el1, nprv.ELEMENT_VAL2 el2, nprv.ELEMENT_VAL3 el3, nprv.ELEMENT_VAL4 el4, nprv.ELEMENT_VAL5 el5, nprv.ELEMENT_VAL6 el6, nprv.ELEMENT_VAL7 el7, nprv.ELEMENT_VAL8 el8, nprv.ELEMENT_VAL9 el9, nprv.ELEMENT_VAL10 el10, nprv.ELEMENT_VAL11 el11, nprv.ELEMENT_VAL12 el12, nprv.ELEMENT_VAL13 el13, nprv.ELEMENT_VAL14 el14, nprv.ELEMENT_VAL15 el15, nprv.ELEMENT_VAL16 el16, nprv.ELEMENT_VAL17 el17, nprv.ELEMENT_VAL18 el18, nprv.ELEMENT_VAL19 el19, nprv.ELEMENT_VAL20 el20, nprv.ELEMENT_VAL21 el21, nprv.ELEMENT_VAL22 el21

FROM XX_PAYROLL_GROUP b, XX_PAYROLL_GROUP_nprv nprv WHERE b.SESSION_ID = USERENV(‘SESSIONID’) and nprv.person_id = b.person_id and nprv.payroll_action_id = b.payroll_action_id order by b.full_name

this is the great and informative site for apps dba, nice work. kindly post query to find the employees department name.

BR Syed Hassan Khalique

use following “Select name from HR_ORGANIZATION_UNITS a where a.ORGANIZATION_ID=:p_org_id”.

Regards, Faisal

faisal your query is not working.

hi, i need to find the full name of the oracle ebs users, from where i find it, please help.

thanks BR SYED HASSAN

i got one requirement from user,user want to display monthly gross pay for employees but parameters period form_date 01-jan-2015 and to_date 31-dec-2015 , i will display montly wise grosspay inforamtion JAN FEB MAR APR JUN JUL AUG SEP OCT NOV DEC 20 20 30 20 20 20 30 20 20 20 20

didn’t compare above values just examples.

not include overtime amount above gross pay payroll, how can i achieve on this.

please help on this.

send me sample code

Leave a 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.

• 1- Accounting Concepts
• 1- Oracle R12 Financial Setup
• 1.1 General Ledger Setup
• 1.1.1 User Creation and Assign Responsibility
• 1.1.2 Chart of Account and Segments Lists
• 1.1.3 Accounting Flexfield
• 1.1.4 Accounting Calendar
• 1.1.4 Define/Enable Currency
• 1.1.5 Accounting Flex Field Value Set
• 1.2 Payable Setup
• 1.2.1 Business Group/Calendar/Inv Org Creation
• Administrator n Concurrent
• Alerts and WorkFlows in Oracle Apps
• Concurrent Programs Related
• Configuring and Administrating Approval Transactions
• Defining HR Responsibility
• Enable Arabic for Reports
• Encumbrance : Accounting Concept Oracle
• Error Messages in Oracle Fusion
• Forms Personalizations
• Grade Key Flexfield Definition
• HR / Payroll Important Tables
• HR / Payroll Related Queries
• Important Tables
• Impratant Queries
• Installing GNU Make For EBS
• Manufacturing
• MO_Global.Set_Policy_Context
• News and Articals
• OAF Personalization
• OAFramework R12X Customization
• OPM Related Queries
• Oracle AME Setup
• Oracle Apps Basics
• Oracle Cloud
• Oracle EBS Documentation Library
• Oracle EBS Shut Down Script for Windows
• Oracle EBS Standard APIs and Interfaces
• Oracle Framework Personalization
• Oracle Fusion
• Oracle HR Fast Formual
• Oracle HRMS / Payroll
• Oracle HRMS / Payroll Setup
• Oracle HRMS Technical
• Oracle I-Recruitment
• Oracle Inventory User Practice
• Oracle OPM Accounting Cycle
• Oracle Purchasing User Practice
• Oracle R12 Form Customiztion
• Oracle R12 Installation on Windows
• Oracle Self-Service Human Resources
• Oracle Trainings
• Oracle XML / BI Publisher
• Report Customization Oracle EBS
• SCM Related Queries
• Self Service
• Setting Item Mandatory
• System Administration
• Tips & Tricks
• Toad Tricks and Tips
• Uncategorized
• Understanding and Using API User Hooks
• What are RICE Components

Recent Comments

• Arun on Oracle BI Publisher Tab issue in MS Word.
• Leo Caipei Chen on Report Customization Oracle R12
• Kavitha Shivaiah on Oracle BI Publisher Tab issue in MS Word.
• ZAFAR IMAM on Oracle SCM Techno Functional Training
• Zafar Imam on Oracle SCM Techno Functional Training

Recent Posts

• Oracle Cloud Support and Help Resources
• Oracle Fusion Error message Legal Entity doesn’t exit (PER-1531116)
• How to create Alerts or send Auto Email or Notification from Oracle Fusion.
• How to Edit Notification in Oracle Fusion.
• Enabling vacation rules and delegating approval responsibilities in Oracle Fusion.

Query to extract ACTION CODE and ACTION REASON

Content (required):

Hello experts,

please, could you share the query to extract the entire list of actions and reasons configured in the system?

Thanks in advance for your time!

Kind Regards,

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

• Category 52
• Configuration
• Category 52-2

Howdy, Stranger!

To view full details, sign in.

Don't have an account? Click here to get started!