APPS.PER_PERUSHIR_XMLP_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 153

      P_UPDATE_STATUS;

Line: 196

  PROCEDURE P_UPDATE_STATUS IS
  BEGIN
    DECLARE
      CURSOR C_PERSON_ID IS
        SELECT
          PPF.PERSON_ID,
          PPF.LAST_NAME,
          PPF.FIRST_NAME
        FROM
          PER_ALL_PEOPLE_F PPF,
          PER_ALL_ASSIGNMENTS_F PAF,
          HR_SOFT_CODING_KEYFLEX HSCF,
          HR_LOCATIONS_ALL HL,
          PER_JOBS JOB,
          PER_PERIODS_OF_SERVICE PPS
        WHERE PPS.PERSON_ID = PPF.PERSON_ID
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS.DATE_START
          AND NVL(PPS.ACTUAL_TERMINATION_DATE
           ,C_END_OF_TIME)
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPF.EFFECTIVE_START_DATE
          AND PPF.EFFECTIVE_END_DATE
          AND PPF.PERSON_ID = PAF.PERSON_ID
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PAF.EFFECTIVE_START_DATE
          AND PAF.EFFECTIVE_END_DATE
          AND HSCF.SEGMENT1 = TO_CHAR(P_TAX_UNIT_ID)
          AND PAF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
          AND PAF.ASSIGNMENT_TYPE = 'E'
          AND PAF.PRIMARY_FLAG = 'Y'
          AND PAF.LOCATION_ID = HL.LOCATION_ID
          AND HL.REGION_2 = NVL(P_STATE_CODE
           ,HL.REGION_2)
          AND PAF.JOB_ID = job.job_id (+)
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between NVL(JOB.DATE_FROM
           ,FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE))
          AND NVL(JOB.DATE_TO
           ,C_END_OF_TIME)
          AND PPF.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
          AND PPF.PER_INFORMATION_CATEGORY = 'US'
          AND PPS.DATE_START <= FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE)
          AND PPF.PER_INFORMATION7 = 'INCL'
        UNION
        SELECT
          PPF.PERSON_ID,
          PPF.LAST_NAME,
          PPF.FIRST_NAME
        FROM
          PER_ALL_PEOPLE_F PPF,
          PER_ALL_ASSIGNMENTS_F PAF,
          HR_SOFT_CODING_KEYFLEX HSCF,
          HR_LOCATIONS_ALL HL,
          PER_JOBS JOB,
          PER_PERIODS_OF_SERVICE PPS
        WHERE PPS.PERSON_ID = PPF.PERSON_ID
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPF.EFFECTIVE_START_DATE
          AND PPF.EFFECTIVE_END_DATE
          AND PPS.ACTUAL_TERMINATION_DATE IS NOT NULL
          AND PPF.PERSON_ID = PAF.PERSON_ID
          AND not exists (
          SELECT
            1
          FROM
            PER_PERIODS_OF_SERVICE PPS2
          WHERE PPF.PERSON_ID = PPS2.PERSON_ID
            AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS2.DATE_START
            AND NVL(PPS2.ACTUAL_TERMINATION_DATE
             ,C_END_OF_TIME) )
          AND PPS.DATE_START = PAF.EFFECTIVE_START_DATE
          AND HSCF.SEGMENT1 = TO_CHAR(P_TAX_UNIT_ID)
          AND PAF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
          AND PAF.ASSIGNMENT_TYPE = 'E'
          AND PAF.PRIMARY_FLAG = 'Y'
          AND PAF.LOCATION_ID = HL.LOCATION_ID
          AND HL.REGION_2 = NVL(P_STATE_CODE
           ,HL.REGION_2)
          AND PAF.JOB_ID = job.job_id (+)
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between NVL(JOB.DATE_FROM
           ,FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE))
          AND NVL(JOB.DATE_TO
           ,C_END_OF_TIME)
          AND PPF.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
          AND PPF.PER_INFORMATION_CATEGORY = 'US'
          AND PPS.DATE_START <= FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE)
          AND PPF.PER_INFORMATION7 = 'INCL'
        ORDER BY
          2,
          3;

Line: 287

      SET_LOCATION('Entered p_update_status'
                  ,5);

Line: 299

        UPDATE
          PER_PEOPLE_F
        SET
          PER_INFORMATION7 = 'DONE'
        WHERE PERSON_ID = V_PERSON_ID
          AND PER_INFORMATION7 = 'INCL';

Line: 307

      SET_LOCATION('p_update_status'
                  ,10);

Line: 311

      SET_LOCATION('Leaving p_update_status'
                  ,15);

Line: 320

  END P_UPDATE_STATUS;

Line: 397

        SELECT
          PPF.PERSON_ID,
          PPF.LAST_NAME,
          PPF.FIRST_NAME,
          SUBSTR(PPF.MIDDLE_NAMES
                ,1
                ,1) MIDDLE_NAME,
          PPF.NATIONAL_IDENTIFIER,
          PPF.DATE_OF_BIRTH,
          PPS.DATE_START
        FROM
          PER_ALL_PEOPLE_F PPF,
          PER_ALL_ASSIGNMENTS_F PAF,
          HR_SOFT_CODING_KEYFLEX HSCF,
          HR_LOCATIONS_ALL HL,
          PER_JOBS JOB,
          PER_PERIODS_OF_SERVICE PPS
        WHERE PPS.PERSON_ID = PPF.PERSON_ID
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS.DATE_START
          AND NVL(PPS.ACTUAL_TERMINATION_DATE
           ,C_END_OF_TIME)
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPF.EFFECTIVE_START_DATE
          AND PPF.EFFECTIVE_END_DATE
          AND PPF.PERSON_ID = PAF.PERSON_ID
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PAF.EFFECTIVE_START_DATE
          AND PAF.EFFECTIVE_END_DATE
          AND HSCF.SEGMENT1 = TO_CHAR(P_TAX_UNIT_ID)
          AND PAF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
          AND PAF.ASSIGNMENT_TYPE = 'E'
          AND PAF.PRIMARY_FLAG = 'Y'
          AND PAF.LOCATION_ID = HL.LOCATION_ID
          AND HL.REGION_2 = NVL(P_STATE_CODE
           ,HL.REGION_2)
          AND PAF.JOB_ID = job.job_id (+)
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between NVL(JOB.DATE_FROM
           ,FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE))
          AND NVL(JOB.DATE_TO
           ,C_END_OF_TIME)
          AND PPF.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
          AND PPF.PER_INFORMATION_CATEGORY = 'US'
          AND PPS.DATE_START <= FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE)
          AND PPF.PER_INFORMATION7 is NULL
        UNION
        SELECT
          PPF.PERSON_ID,
          PPF.LAST_NAME,
          PPF.FIRST_NAME,
          SUBSTR(PPF.MIDDLE_NAMES
                ,1
                ,1) MIDDLE_NAME,
          PPF.NATIONAL_IDENTIFIER,
          PPF.DATE_OF_BIRTH,
          PPS.DATE_START
        FROM
          PER_ALL_PEOPLE_F PPF,
          PER_ALL_ASSIGNMENTS_F PAF,
          HR_SOFT_CODING_KEYFLEX HSCF,
          HR_LOCATIONS_ALL HL,
          PER_JOBS JOB,
          PER_PERIODS_OF_SERVICE PPS
        WHERE PPS.PERSON_ID = PPF.PERSON_ID
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPF.EFFECTIVE_START_DATE
          AND PPF.EFFECTIVE_END_DATE
          AND PPS.ACTUAL_TERMINATION_DATE IS NOT NULL
          AND PPF.PERSON_ID = PAF.PERSON_ID
          AND not exists (
          SELECT
            1
          FROM
            PER_PERIODS_OF_SERVICE PPS2
          WHERE PPF.PERSON_ID = PPS2.PERSON_ID
            AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS2.DATE_START
            AND NVL(PPS2.ACTUAL_TERMINATION_DATE
             ,C_END_OF_TIME) )
          AND PPS.DATE_START = PAF.EFFECTIVE_START_DATE
          AND HSCF.SEGMENT1 = TO_CHAR(P_TAX_UNIT_ID)
          AND PAF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
          AND PAF.ASSIGNMENT_TYPE = 'E'
          AND PAF.PRIMARY_FLAG = 'Y'
          AND PAF.LOCATION_ID = HL.LOCATION_ID
          AND HL.REGION_2 = NVL(P_STATE_CODE
           ,HL.REGION_2)
          AND PAF.JOB_ID = job.job_id (+)
          AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between NVL(JOB.DATE_FROM
           ,FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE))
          AND NVL(JOB.DATE_TO
           ,C_END_OF_TIME)
          AND PPF.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
          AND PPF.PER_INFORMATION_CATEGORY = 'US'
          AND PPS.DATE_START <= FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE)
          AND PPF.PER_INFORMATION7 is NULL
        ORDER BY
          2,
          3;

Line: 526

                           ,'Warning : Please update the New Hire field.');