APPS.GHR_EXEMP_PURGE SQL Statements

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

Line: 17

      SELECT full_name, employee_number, national_identifier, person_id, effective_start_date  FROM PER_ALL_PEOPLE_F WHERE EFFECTIVE_START_DATE <= l_cutoff_dt
      AND   BUSINESS_GROUP_ID = l_business_group
      AND   PERSON_TYPE_ID IN (SELECT PERSON_TYPE_ID FROM PER_PERSON_TYPES
                               WHERE BUSINESS_GROUP_ID = l_business_group AND
                               SYSTEM_PERSON_TYPE in ('EX_EMP', 'EX_CWK'))
      AND   TRUNC(EFFECTIVE_END_DATE) = FND_DATE.CANONICAL_TO_DATE('4712/12/31');

Line: 26

      SELECT trunc(months_between(sysdate,p_eff_date)/12) years,
             trunc(months_between(sysdate,p_eff_date)) - (12 * trunc(months_between(sysdate,p_eff_date)/12)) months
      FROM DUAL;

Line: 32

    SELECT value
    FROM  v$parameter
    WHERE name = 'utl_file_dir';

Line: 152

        l_log_proc := 'Records successfully Deleted';

Line: 269

      SELECT 1
      FROM   FND_USER
      WHERE  employee_id = p_person_id;

Line: 282

     UPDATE FND_USER
     SET employee_id = NULL
     WHERE employee_id = p_person_id;

Line: 300

        SELECT  1
	FROM    GHR_PA_REQUESTS par
	WHERE   (NVL(AUTHORIZED_BY_PERSON_ID,-1) = p_person_id OR NVL(REQUESTED_BY_PERSON_ID,-1) = p_person_id
	         OR NVL(APPROVING_OFFICIAL_FULL_NAME,'@') = p_full_name)
	AND     PA_NOTIFICATION_ID IS NOT NULL
	AND     NOT EXISTS(SELECT 1 FROM GHR_PA_REQUESTS
	            WHERE ALTERED_PA_REQUEST_ID = par.PA_REQUEST_ID
		    AND   PA_NOTIFICATION_ID IS NOT NULL
		    AND   FIRST_NOA_CODE = '001');

Line: 334

  l_delete_permitted varchar2(1);

Line: 346

  SELECT count(*)
  INTO   l_person_types
  FROM   PER_PERSON_TYPE_USAGES_F ptu
  WHERE  ptu.person_id     = p_person_id
  AND    ptu.effective_start_date >= p_effective_date;

Line: 361

    SELECT 'Y'
    INTO   l_delete_permitted
    FROM   sys.dual
    WHERE  not exists (
           SELECT null
           FROM   PER_CONTACT_RELATIONSHIPS r
           WHERE  r.person_id     = p_person_id
           OR     r.contact_person_id = p_person_id);

Line: 379

    SELECT 'Y'
    INTO   l_delete_permitted
    FROM   sys.dual
    WHERE  1 >= (
           SELECT count(*)
           FROM   per_assignments_f a
           WHERE  a.person_id = P_PERSON_ID
           AND    a.effective_start_date >= p_effective_date);

Line: 398

    SELECT 'Y'
    INTO   l_delete_permitted
    FROM   sys.dual
    WHERE  not exists (
           SELECT null
           FROM   PER_ASSIGNMENTS_F a
           WHERE  a.recruiter_id  = p_person_id
           OR     a.supervisor_id = p_person_id);

Line: 417

    select 'Y'
    into   l_delete_permitted
    from   sys.dual
    where  not exists (
           select null
           from   per_secondary_ass_statuses s
           where  exists (
                  select null
                  from   per_assignments_f a
                  where  a.person_id = P_PERSON_ID
                  and    a.assignment_id = s.assignment_id));

Line: 449

   HR_PERSON_API.DELETE_PERSON
              (
                p_validate        => FALSE
		,p_effective_date  => SYSDATE
		,p_person_id       => p_person_id
		,p_perform_predel_validation  => FALSE
		,p_person_org_manager_warning  => l_org_mgr);

Line: 469

   DELETE FROM PAY_ELEMENT_ENTRIES_F
   WHERE ELEMENT_ENTRY_ID IN
     (SELECT ELEMENT_ENTRY_ID FROM PAY_ELEMENT_ENTRY_VALUES_F WHERE
      ELEMENT_ENTRY_VALUE_ID IN (SELECT ELEMENT_ENTRY_VALUE_ID FROM BEN_PRTT_RT_VAL WHERE PRTT_ENRT_RSLT_ID
                                 IN (SELECT PRTT_ENRT_RSLT_ID FROM BEN_PRTT_ENRT_RSLT_F WHERE PERSON_ID =
                                     P_PERSON_ID)));

Line: 476

   DELETE FROM PAY_ELEMENT_ENTRY_VALUES_F
   WHERE ELEMENT_ENTRY_VALUE_ID IN
      (SELECT ELEMENT_ENTRY_VALUE_ID FROM BEN_PRTT_RT_VAL
       WHERE PRTT_ENRT_RSLT_ID IN (SELECT PRTT_ENRT_RSLT_ID FROM BEN_PRTT_ENRT_RSLT_F WHERE PERSON_ID =
                                   P_PERSON_ID));

Line: 482

   BEN_PERSON_DELETE.DELETE_BEN_ROWS (p_person_id => p_person_id);

Line: 493

      SELECT PA_REQUEST_ID FROM
      GHR_PA_REQUESTS
      WHERE PERSON_ID = p_person_id;

Line: 499

      SELECT COMPLAINT_ID FROM
      GHR_COMPLAINTS2
      WHERE COMPLAINANT_PERSON_ID  = p_person_id;

Line: 514

     DELETE FROM GHR_PA_REQUEST_SHADOW
     WHERE PA_REQUEST_ID = rpa_action.PA_REQUEST_ID;

Line: 517

     DELETE FROM GHR_PA_REQUEST_EXTRA_INFO
     WHERE PA_REQUEST_ID = rpa_action.PA_REQUEST_ID;

Line: 520

     DELETE FROM GHR_PA_REQUEST_EI_SHADOW
     WHERE PA_REQUEST_ID = rpa_action.PA_REQUEST_ID;

Line: 523

     DELETE FROM GHR_PA_REMARKS
     WHERE PA_REQUEST_ID = rpa_action.PA_REQUEST_ID;

Line: 526

     DELETE FROM GHR_PA_ROUTING_HISTORY
     WHERE PA_REQUEST_ID = rpa_action.PA_REQUEST_ID;

Line: 529

     DELETE FROM GHR_PA_HISTORY
     WHERE (PA_REQUEST_ID = rpa_action.PA_REQUEST_ID
            OR
            PERSON_ID = p_person_id);

Line: 538

  DELETE FROM GHR_PA_REQUESTS
  WHERE PERSON_ID = p_person_id;

Line: 544

    DELETE FROM GHR_COMPL_PEOPLE
    WHERE  COMPLAINT_ID = compl_ppl.COMPLAINT_ID;

Line: 548

  DELETE FROM GHR_COMPLAINTS2
  WHERE  COMPLAINANT_PERSON_ID  = p_person_id;

Line: 551

  DELETE FROM GHR_COMPL_PEOPLE
  WHERE  PERSON_ID = p_person_id;

Line: 554

  DELETE FROM GHR_RIF_REGISTERS
  WHERE  PERSON_ID = p_person_id;

Line: 565

     UPDATE GHR_PA_REQUESTS
     SET REQUESTED_BY_FULL_NAME = p_full_name,
         REQUESTED_BY_PERSON_ID = NULL
     WHERE REQUESTED_BY_PERSON_ID = p_person_id;

Line: 570

     UPDATE GHR_PA_REQUESTS
     SET AUTHORIZED_BY_FULL_NAME = p_full_name,
         AUTHORIZED_BY_PERSON_ID = NULL
     WHERE AUTHORIZED_BY_PERSON_ID = p_person_id;