We have a HR survey checklist that gets assigned to employees every six months. The checklist has a questionnaire that the employee has to answer with their reponses every six months.
Report requirement - We need a report that pulls out the latest responses for the current allocated checklist for an employee.
Problem - The main tables that hold this information are PER_ALLOCATED_CHECKLISTS ( this holds a row everytime a checklist is assigned to an employee. allocated_checklist_id is key here) and employee responses are stored in HRQ_QSTNR_PCPT_RESPONSES_V .( holds employees answers to each question in the questionnaire)
Now HRQ_QSTNR_PCPT_RESPONSES_V has no reference to allocated_checklist_id. So if an employee answers every six months, there is no reference to which checklist assignment it corresponds to.
Does anybody know the link between an allocated checklist and their corresponding responses ?
Appreciate your time to go through this.