Skip to main content

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

In this session we are going to see how to fetch Oracle Cloud HCM Checklist Questionnaire data using SQL.

SQL Text:

with people_tbl_vw as ( select papf.person_id ,papf.person_number from per_all_people_f papf where 1 = 1 and sysdate between papf.effective_start_date and papf.effective_end_date and papf.person_id = :P_PERSON_ID ) ,person_name_tbl_vw as ( select ptv.person_id ,ppnf.display_name from people_tbl_vw ptv ,per_person_names_f ppnf where 1 = 1 and ppnf.person_id = ptv.person_id and ppnf.name_type = 'GLOBAL' and sysdate between ppnf.effective_start_date and ppnf.effective_end_date ) ,ppos_tbl_vw as ( select ptv.person_id ,max(to_char(actual_termination_date,'dd-Mon-yyyy','nls_date_language=english')) LWD from people_tbl_vw ptv ,per_periods_of_service ppos where 1 = 1 and ppos.person_id = ptv.person_id group by ptv.person_id ) ,email_tbl_vw as ( select * from ( select ptv.person_id ,pea.email_type ,pea.email_address from people_tbl_vw ptv ,per_email_addresses pea where pea.person_id = ptv.person_id and pea.email_type in ('H1','W1') and sysdate between pea.date_from and coalesce(pea.date_to,to_date('4712-12-31')) ) pivot (max(email_address) as email for email_type in ( 'H1' home ,'W1' wrk )) ) select ptv.person_number ,pntv.display_name ,pptv.LWD ,etv.home_email ,etv.wrk_email ,pcv.name checklist_name ,patv.task_name ,per_extract_utility.get_decoded_lookup('PER_CHECKLIST_TASK_STATUS',pat.status) status ,pat.task_comments ,hqv.question_text ,coalesce(hqav.long_text,hqprv.answer_text) response from people_tbl_vw ptv ,person_name_tbl_vw pntv ,ppos_tbl_vw pptv ,email_tbl_vw etv ,per_allocated_checklists pac ,per_checklists_vl pcv ,per_allocated_tasks pat ,per_allocated_tasks_vl patv ,hrq_qstnr_all_qstns_v hqaqv ,hrq_questions_vl hqv ,hrq_ptcpnt_responses_v hqprv ,hrq_qstn_answers_vl hqav where 1 = 1 and pntv.person_id = ptv.person_id and pptv.person_id(+) = ptv.person_id and etv.person_id(+) = ptv.person_id and pac.person_id = ptv.person_id and pcv.checklist_id = pac.checklist_id and pat.allocated_checklist_id = pac.allocated_checklist_id and patv.allocated_task_id = pat.allocated_task_id and hqaqv.questionnaire_id = pat.questionnaire_id and hqv.question_id = hqaqv.question_id and hqv.qstn_version_num = hqaqv.qstn_version_num and hqprv.questionnaire_id(+) = pat.questionnaire_id and hqprv.qstnr_question_id(+) = hqaqv.qstnr_question_id and hqav.qstn_answer_id(+) = hqprv.qstn_answer_id and pat.document_entity_id = hqprv.participant_id(+) and pac.allocated_checklist_id = :P_ALLOCATED_CHECKLIST_ID and (coalesce(null,:P_QSTN_CD) is null or hqv.question_code in (:P_QSTN_CD))

Labels:

Comments

Post a Comment

Popular posts from this blog

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 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 SourceSystemId Q
3 comments
Read more

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

In this blog, we are going to see how to generate output using Excel Templates 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 Excel file and save it as type "Excel 97-2003 Workbook" (.xls) I have created 3 tabs "Summary", "Detail" and "XDO_METADATA" XDO_METADATA tab is mandatory. Place the below text in XDO_METADATA tab Version ARU-dbdrv Extractor Version Template Code Template Type TYPE_EXCEL_TEMPLATE Preprocess XSLT File Last Modified Date Last Modified By Data Constraints: In the "Detail" tab create Header row as shown below Now add the XDO tags Select the blank &quo
2 comments
Read more