Skip to main content

Oracle Cloud HCM - OTL Sample SQL

Below is the Fusion OTL Sample SQL, Shift Premium used in the SQL is the Custom Attribute. Use Custom Attributes based on your requirements.  
SQL Text: 
select papf.person_number Employee_Number ,ppnf.full_name Employee_Name ,(select distinct ppnf_mgr.full_name from per_assignment_supervisors_f pasf ,per_person_names_f ppnf_mgr where 1 = 1 and pasf.assignment_id = paam.assignment_id and pasf.manager_type = 'LINE_MANAGER' and pasf.primary_flag = 'Y' and ppnf_mgr.person_id = pasf.manager_id and ppnf_mgr.name_type = 'GLOBAL' and sysdate between pasf.effective_start_date and pasf.effective_end_date and sysdate between ppnf_mgr.effective_start_date and ppnf_mgr.effective_end_date) supervisor_name ,(select dept.name from hr_organization_v dept where 1 = 1 and sysdate between effective_start_date and effective_end_date and dept.organization_id = paam.organization_id and rownum = 1) Department ,to_char(rec.start_time,'FmDay, Month dd','nls_date_language = English') Day ,to_char(rec.start_time,'hh:mi am') In_Time ,to_char(rec.stop_time,'hh:mi am') Out_Time ,round(rec.measure,2) Quantity ,(select attribute_varchar6 from hwm_ext_attrb_detail_v attrb1 where 1 = 1 and attrb1.usages_source_id = rec.tm_rec_id and attrb1.usages_source_version = rec.tm_rec_version and attribute_category = 'ORA_CUSTOM' and rownum = 1) Shift_Premium from per_all_people_f papf ,per_all_assignments_m paam ,per_person_names_f ppnf ,hwm_tm_rec rec ,hwm_tm_rec_grp trg ,hwm_tm_rec_grp trd ,hwm_tm_rec_grp_usages usg ,Hwm_Tm_Rep_Atrb_Usages atrbusg ,hwm_tm_rep_atrbs atrbs ,hwm_tm_atrb_flds_vl flds ,hwm_tm_approved_ts_v status where 1 = 1 and sysdate between papf.effective_start_date and papf.effective_end_date and sysdate between paam.effective_start_date and paam.effective_end_date and sysdate between ppnf.effective_start_date and ppnf.effective_end_date and status.approved_timestamp between trg.date_from and trg.date_to and status.approved_timestamp between trd.date_from and trd.date_to and status.approved_timestamp between rec.date_from and rec.date_to and paam.person_id = papf.person_id and ppnf.person_id = papf.person_id and rec.resource_id = papf.person_id and usg.tm_rec_id = rec.orig_tm_rec_id and usg.tm_rec_version = rec.orig_tm_rec_version and trd.tm_rec_grp_id = usg.tm_rec_grp_id and trd.tm_rec_grp_version = usg.tm_rec_grp_version and trg.tm_rec_grp_id = trd.parent_tm_rec_grp_id and trg.tm_rec_grp_version = trd.parent_tm_rec_grp_version and status.tm_bldg_blk_id = trg.tm_rec_grp_id and status.tm_bldg_blk_version = trg.tm_rec_grp_version and atrbusg.usages_source_id = rec.tm_rec_id and atrbusg.usages_source_version = rec.tm_rec_version and trbusg.tm_rep_atrb_id = atrbs.tm_rep_atrb_id and flds.tm_atrb_fld_id = atrbs.master_attribute_id and paam.assignment_type = 'E' and paam.effective_latest_change = 'Y' and paam.primary_flag = 'Y' and paam.assignment_status_type = 'ACTIVE' -- and paam.legislation_code = 'IN' and ppnf.name_type = 'GLOBAL' and rec.delete_flag is null and rec.orig_tm_rec_id is not null and trd.delete_flag is null and trg.delete_flag is null and flds.name = 'PayrollTimeType' and rec.start_time >= :p_date_from and rec.stop_time <= :p_date_to
Labels:

Comments

Post a Comment

Popular posts from this blog

Do you know how to cancel the Work Relationship using HDL in Oracle Cloud HCM?

In this blog, we are going to see how to cancel the Work Relationship using HDL (HCM Data Loader) Please make sure you have taken the back up of all the necessary data before cancelling the Work Relationship To cancel the Work Relationship, we use HDL Source Key method. Use the below sample template for reference SET PURGE_FUTURE_CHANGES N METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|PersonNumber|DateStart|WorkerType|CancelWorkRelationshipFlag DELETE|WorkRelationship|FUSION|1234|WRK_1234|Legal_Employer|1234|2018/02/01|E|Y In the above template, we do not know the values of the source keys SourceSystemOwner SourceSystemId PersonId(SourceSystemId) Use below query to fetch the SourceSystemOwner Query: select source_system_owner from hrc_integration_key_map where surrogate_id = period_of_service_id   -- pass period_of_service_id corresponding to Work Relationship Use below query to fetch the SourceSy...
3 comments
Read more

Do you know how to generate output using eText Template in Oracle Cloud BI Publisher?

In this blog, we are going to see how to generate output using eText Template in Fusion BI Publisher I am going to show this using sample data. This is my sample data Click "Export" button to download the XML file. This is my XML File Open a blank Word Document and save it as type "Rich Text Format" Add a format setup which is given below <TEMPLATE TYPE> FIXED_POSITION_BASED <OUTPUT CHARACTER SET> UTF-8 <NEW RECORD CHARACTER> Carriage Return Add a Header table which is given below. For headers, give the header name within quotes ex: 'Emplid'. For headers, use the level DATA_DS which is the top level and appears only once in the XML <LEVEL> DATA_DS <POSITION> <LENGTH> <FORMAT> <PAD> <DATA> <COMMENTS> <NEW RECORD> DATA_DS Alpha ‘Emplid’ ...
20 comments
Read more

Do you know how to fetch Oracle Cloud HCM Performance Questionnaire data using SQL

In this session, we are going to see how to how to fetch Oracle Cloud HCM Performance Questionnaire data using SQL SQL Text: with response_view as ( select ep . evaluation_id , ep . person_id , ep . role_type_code , pq . qstnr_question_id , pq . question_type , pq . question_text question , coalesce ( qr . answer_text , qa . long_text ) answer from hra_eval_participants ep , hrq_qstnr_participants qp , hrq_eval_ptcpnt_questions_v epq , hrq_ptcpnt_questions_v pq , hrq_qstnr_latest_resps_v qnlr , hrq_qstn_responses qr , hrq_all_qstn_answers_v qa where 1 = 1 and qp . participant_id = to_char ( ep . eval_participant_id ) and epq . participant_id = qp . participant_id and epq . subject_id = qp . subject_id and pq . qstnr_section_id = epq . qstnr_section_id and pq . qstnr_question_id = epq . qstnr_question_id and qnlr . subject_id = to_char...
Post a Comment
Read more