SQL Query to extract Worker's Assignment Supervisor Detail in Oracle R12 - Orapoint

Overview: Below SQL Query is for extracting Worker’s Assignment Supervisor Detail for data migration from EBS to Fusion through FBDI Template, Below SQL Query column arrangement done as per Fusion FBDI template to import data from EBS to Fusion.

SELECT DISTINCT NULL                                                                 "AssignmentSupervisorId"
                ,NULL                                                                 "AssignmentId"
       ,(SELECT  min (to_char(min.effective_start_date,'YYYY/MM/DD'))
           FROM  per_all_assignments_f min
          WHERE  min.assignment_type in ('E','C')  
          START WITH ( min.assignment_id = paaf.assignment_id
                   AND min.effective_start_date = paaf.effective_start_date
                   AND min.effective_end_date = paaf.effective_end_date
                     )
         CONNECT BY PRIOR effective_start_date - 1 = effective_end_date
             AND     nvl (PRIOR supervisor_id
                                         ,1) = nvl (supervisor_id
                                                          ,1)
             AND     PRIOR assignment_id = assignment_id
          )                                                                          "EffectiveStartDate"
     ,( SELECT  max (to_char(max.effective_end_date,'YYYY/MM/DD'))
                       FROM    per_all_assignments_f max
                      WHERE max.assignment_type in ('E','C') 
                         START WITH (
                                    max.assignment_id = paaf.assignment_id
                              AND     max.effective_start_date = paaf.effective_start_date
                                     AND     max.effective_end_date = paaf.effective_end_date
                                     )
                             CONNECT BY PRIOR effective_end_date + 1 = effective_start_date
                             AND     nvl (PRIOR supervisor_id
                                         ,1) = nvl (supervisor_id
                                                          ,1)
                             AND     PRIOR assignment_id = assignment_id
                             )                                                          "EffectiveEndDate"
       ,NULL                                                                           "ManagerAssignmentId"               
       --,NULL                                                        "ManangerId"    
       ,'LINE_MANAGER'                                                                 "ManagerType"   
       ,TO_CHAR(papf.person_id)||'PERSON'                                           "PersonId(SourceSystemId)"       ,'Y'                                                                            "PrimaryFlag"       ,DECODE(ppt.system_person_type,               'EX_CWK','C'||paaf.assignment_number,               'CWK','C'||paaf.assignment_number,               'E'||paaf.assignment_number)                                          "AssignmentNumber"        ,(select paaf1.assignment_type||paaf1.assignment_number            from per_all_assignments_f paaf1           where paaf1.person_id = papf_mgr.person_id              and paaf1.primary_flag = 'Y'             and assignment_type in ('E','C')             AND paaf.effective_end_date between paaf1.effective_start_date and paaf1.effective_end_date) "ManagerAssignmentNumber"       ,NULL                                                     "ManagerPersonNumber"       ,'MANAGER_CHANGE'                                            "ActionCode"        ,NULL                                                     "ReasonCode"       ,NULL                                                     "NewManagerType"       ,NULL                                                     "NewManagerPersonNumber"       ,NULL                                                     "NewManagerAssignmentNumber"           ,NULL                                                     "GUID"       ,(SELECT NAME                                                   FROM V$DATABASE )                                     "SourceSystemOwner",      'SUPERVISOR'||TO_CHAR(paaf.assignment_id)                                       "SourceSystemId"    
  FROM per_all_assignments_f paaf,
       per_all_people_f papf,
       per_all_people_f papf_mgr,
       per_person_type_usages_f pptuf,
       per_person_types ppt,
       per_person_type_usages_f pptuf_mgr,
       per_person_types ppt_mgr,
       per_business_groups pbg
  WHERE papf.person_id = pptuf.person_id
    AND pptuf.person_type_id = ppt.person_type_id
    AND PPT.SYSTEM_PERSON_TYPE in ('EX_CWK','EX_EMP','EMP','CWK','EMP_APL')        
    AND papf.business_group_id  = pbg.business_group_id
    AND ppt.business_group_id = pbg.business_group_id      
    AND pbg.business_group_id = 81 --202 --added on 19-dec-2019 by nilesh
    --AND papf.person_id IN (221,9037)
    --AND papf.person_id IN (18,19,20,21)
    AND paaf.business_group_id = pbg.business_group_id
    AND paaf.person_id = papf.person_id
    AND paaf.supervisor_id = papf_mgr.person_id
    AND TRUNC(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
    AND TRUNC(sysdate) BETWEEN papf_mgr.effective_start_date AND papf_mgr.effective_end_date
    --AND TRUNC(papf.effective_start_date) BETWEEN paaf.effective_start_date AND paaf.effective_end_date 
    AND TRUNC(papf.effective_start_date) BETWEEN pptuf.effective_start_date and pptuf.effective_end_date  
    and papf_mgr.person_id = pptuf_mgr.person_id
    AND pptuf_mgr.person_type_id = ppt_mgr.person_type_id
    AND PPT_mgr.SYSTEM_PERSON_TYPE in ('EX_CWK','EX_EMP','EMP','CWK','EMP_APL')
    AND ppt_mgr.business_group_id = pbg.business_group_id
    AND TRUNC(papf_mgr.effective_start_date) BETWEEN pptuf_mgr.effective_start_date and pptuf_mgr.effective_end_date 
    AND paaf.assignment_type IN ('E','C') 
    --AND papf.person_id IN (18,19,20,21)
    --AND papf.person_id IN (6506)
    ORDER BY "AssignmentNumber";