Assignments Query

SELECT distinct per.person_number,
            (SELECT DISTINCT NAME FROM HR_ALL_ORGANIZATION_UNITS_F_VL HOU
            WHERE HOU.ORGANIZATION_ID = ASG.LEGAL_ENTITY_ID
            AND  TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN HOU.EFFECTIVE_START_DATE AND HOU.EFFECTIVE_END_DATE) LEGAL_EMPLOYER,
                ASG.ASSIGNMENT_NUMBER,
                PPN.FULL_NAME,
                ASG.EFFECTIVE_START_DATE,
                ASG.EFFECTIVE_END_DATE,               
                (SELECT DISTINCT NAME
                FROM HR_ALL_ORGANIZATION_UNITS_F_VL  HOU
                WHERE HOU.ORGANIZATION_ID = ASG.BUSINESS_UNIT_ID
                AND  TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN HOU.EFFECTIVE_START_DATE
                AND HOU.EFFECTIVE_END_DATE) BUSINESS_UNIT,
                (select distinct group_name from PER_PEOPLE_GROUPS  where PEOPLE_GROUP_ID = ASG.PEOPLE_GROUP_ID) PEOPLE_GROUP_NAME,
                INITCAP (TO_CHAR (TO_DATE (ASG.NOTICE_PERIOD, 'jsp'), 'jsp')) "NOTICE_PERIOD",
                (select distinct meaning
                from fnd_lookup_values
                where lookup_type = 'ANC_DURATION_UOM'
                and lookup_code = ASG.NOTICE_PERIOD_UOM) "NOTICE_PERIOD_UOM",
                (select DISTINCT NAME FROM PER_GRADES PG
                WHERE PG.GRADE_ID= ASG.GRADE_ID
                AND TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN NVL(PG.EFFECTIVE_START_DATE, TO_DATE('1900-01-01', 'YYYY.MM.DD'))
                AND NVL(PG.EFFECTIVE_END_DATE, TO_DATE('4712-12-31', 'YYYY.MM.DD'))) GRADE,
                (select distinct meaning from fnd_lookup_values where lookup_type = 'EMP_CAT'
                and lookup_code=ASG.EMPLOYMENT_CATEGORY) ASSIGNMENT_CATEGORY,
                (SELECT DISTINCT PJ.NAME FROM PER_JOBS_F_VL PJ
                WHERE PJ.JOB_ID = ASG.JOB_ID
                AND TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN NVL(PJ.EFFECTIVE_START_DATE, TO_DATE('1900-01-01', 'YYYY.MM.DD'))
                AND NVL(PJ.EFFECTIVE_END_DATE, TO_DATE('4712-12-31', 'YYYY.MM.DD'))) JOB,
                (SELECT DISTINCT LOCATION_NAME FROM HR_LOCATIONS_ALL HL
                WHERE HL.LOCATION_ID=ASG.LOCATION_ID
                AND TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN HL.EFFECTIVE_START_DATE AND HL.EFFECTIVE_END_DATE) LOCATION,
                (SELECT DISTINCT NAME FROM HR_ALL_ORGANIZATION_UNITS_F_VL HOU
                WHERE HOU.ORGANIZATION_ID = ASG.ORGANIZATION_ID
                AND  TRUNC(ASG.EFFECTIVE_START_DATE) BETWEEN HOU.EFFECTIVE_START_DATE AND HOU.EFFECTIVE_END_DATE) DEPARTMENT,
                ASG.PROBATION_PERIOD,
                (select distinct meaning from fnd_lookup_values where lookup_type = 'QUALIFYING_UNITS' and lookup_code = ASG.PROBATION_UNIT) PROBATION_UNIT,
                ASG.Manager_Flag,
                (SELECT DISTINCT PP.NAME FROM HR_ALL_POSITIONS_F_TL PP
                WHERE PP.POSITION_ID = ASG.POSITION_ID AND TRUNC(ASG.EFFECTIVE_START_DATE)
                BETWEEN NVL(PP.EFFECTIVE_START_DATE, TO_DATE('1900-01-01', 'YYYY.MM.DD'))
                AND NVL(PP.EFFECTIVE_END_DATE, TO_DATE('4712-12-31', 'YYYY.MM.DD'))) POSITION,
                'LINE_MANAGER' MANAGER_TYPE,
                (select distinct ASSIGNMENT_NUMBER from per_all_assignments_f
                where trunc(sysdate) between effective_start_date and effective_end_date
                and ASSIGNMENT_TYPE = 'E'
                and assignment_id = (select  distinct MANAGER_ASSIGNMENT_ID 
                                     from PER_ASSIGNMENT_SUPERVISORS_F
                                    where trunc(sysdate) between effective_start_date and effective_end_date
                                    AND MANAGER_TYPE = 'LINE_MANAGER'
                                    and person_id = PER.person_id)) MANAGER_ASSIGNMENT_NUMBER,
                (SELECT DISTINCT  PERSON_NUMBER 
                  FROM PER_ALL_PEOPLE_F PPL,
                              PER_ASSIGNMENT_SUPERVISORS_F ASGSUP
                 WHERE PPL.PERSON_ID=ASGSUP.MANAGER_ID
                      AND ASGSUP.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
                      AND TRUNC(ASG.EFFECTIVE_START_DATE) between ppl.effective_start_date and ppl.effective_end_date
                      AND TRUNC(ASG.EFFECTIVE_START_DATE) between ASGSUP.effective_start_date and ASGSUP.effective_end_date
                      AND MANAGER_TYPE = 'LINE_MANAGER'
                      and rownum = 1) MANAGER_NO,
                    (SELECT DISTINCT  FULL_NAME
                  FROM PER_PERSON_NAMES_F PPL,
                              PER_ASSIGNMENT_SUPERVISORS_F ASGSUP
                 WHERE PPL.PERSON_ID=ASGSUP.MANAGER_ID
                      AND ASGSUP.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
                      AND TRUNC(ASG.EFFECTIVE_START_DATE) between ppl.effective_start_date and ppl.effective_end_date
                      AND TRUNC(ASG.EFFECTIVE_START_DATE) between ASGSUP.effective_start_date and ASGSUP.effective_end_date
                      AND MANAGER_TYPE = 'LINE_MANAGER'
                      and rownum = 1) MANAGER_NAME,
                (select distinct person_number from per_person_names_f ppnf
        where trunc(sysdate) between effective_start_date and effective_end_date
        AND  PPN.NAME_TYPE = 'GLOBAL'
        and person_id =
        (select  distinct MANAGER_ID 
                from PER_ASSIGNMENT_SUPERVISORS_F
                where trunc(sysdate) between effective_start_date and effective_end_date
                AND Manager_type = 'DEPT_MGR'
                and person_id = per.person_id)) DEP_MANAGER_NUM,
                (SELECT distinct salary_amount FROM CMP_SALARY WHERE person_id =per.person_id
                 AND trunc(sysdate) BETWEEN date_from AND date_to
                    )   "BASIC_SALARY",
                        ASG.ASS_ATTRIBUTE3 AIR_TICKET_DESTINATION
    FROM PER_PEOPLE_F PER,
                PER_PERSON_NAMES_F PPN,
                PER_ALL_ASSIGNMENTS_F ASG,
                PER_PERIODS_OF_SERVICE PPS
 WHERE PER.PERSON_ID = PPN.PERSON_ID
      AND  PER.PERSON_ID = ASG.PERSON_ID
      AND  PER.PERSON_ID = PPS.PERSON_ID
      AND  ASG.ASSIGNMENT_TYPE = 'E'
      AND  TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
      AND  TRUNC(SYSDATE) BETWEEN PPN.EFFECTIVE_START_DATE AND PPN.EFFECTIVE_END_DATE
      AND  PPN.NAME_TYPE = 'GLOBAL'
      ORDER BY PER.PERSON_NUMBER