APPS.JA_CN_HRMS_EXPORT_PKG SQL Statements

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

Line: 44

SELECT meaning
  FROM fnd_lookup_values
 WHERE lookup_type = p_type
   AND enabled_flag = 'Y'
   AND lookup_code = p_code
   AND LANGUAGE = userenv('lang')
 ORDER BY lookup_code;

Line: 82

SELECT '1'
  FROM PER_ALL_PEOPLE_F
 WHERE PERSON_ID = p_person_id
   AND p_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 89

SELECT NATIONAL_IDENTIFIER
  FROM PER_ALL_PEOPLE_F
 WHERE PERSON_ID = p_person_id
   AND p_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 96

SELECT PEI_INFORMATION1 A, PEI_INFORMATION2 B
  FROM PER_PEOPLE_EXTRA_INFO
 WHERE PERSON_ID = p_person_id
   AND INFORMATION_TYPE = 'PER_PASS_INFO_CN'
   AND PEI_INFORMATION_CATEGORY = 'PER_PASS_INFO_CN'
   ORDER BY A;

Line: 105

SELECT PEI_INFORMATION2
  FROM PER_PEOPLE_EXTRA_INFO
 WHERE PERSON_ID = p_person_id
   AND INFORMATION_TYPE = 'PER_PASSPORT_INFO_CN'
   AND PEI_INFORMATION_CATEGORY = 'PER_PASSPORT_INFO_CN';

Line: 158

         IF l_passport IS NULL THEN --Updated for fixing bug# 9742065
             CLOSE c_get_passport_no;

Line: 198

SELECT  POSE.organization_id_parent organization_id
   FROM   per_org_structure_elements POSE
         ,per_organization_structures POS
         ,per_org_structure_versions POSV
  WHERE   POSV.org_structure_version_id = POSE.org_structure_version_id
    AND   POS.primary_structure_flag='Y'
    AND   POS.organization_structure_id = POSV.organization_structure_id
    AND   POSE.organization_id_child      = p_organization_id
    AND   EXISTS (SELECT 1
                    FROM hr_organization_information info
                   WHERE info.org_information1		= 'HR_ORG'
                     AND info.org_information_context	= 'CLASS'
                     AND info.organization_id = POSE.organization_id_parent
                     AND info.org_information2		= 'Y');

Line: 265

SELECT MIN(GP.START_DATE),
       MAX(GP.END_DATE)
  FROM GL_PERIODS GP,
       GL_LEDGERS LED
 WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
   AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
   AND LED.LEDGER_ID = pn_ledger_id --parameter: pn_ledger_id
   AND GP.PERIOD_YEAR = TO_NUMBER(pv_accounting_year); --parameter: pv_accounting_year

Line: 278

  SELECT orgtl.NAME          DEPARTMENT_NAME,
         org.organization_id DEPARMENT_ID,
         NULL                PARENT_DEPARTMENT_ID
    FROM hr_organization_units org, hr_all_organization_units_tl orgtl
   WHERE orgtl.organization_id = org.organization_id
     AND orgtl.LANGUAGE = userenv('LANG')
     AND org.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
     AND date_from <= pd_acc_end_date
     AND nvl(date_to,
             pd_acc_end_date) >= pd_acc_start_date
     AND EXISTS (SELECT 1
            FROM hr_organization_information info
           WHERE info.org_information1 = 'HR_ORG'
             AND info.org_information_context = 'CLASS'
             AND info.organization_id = org.organization_id
             AND info.org_information2 = 'Y');

Line: 459

SELECT MIN(GP.START_DATE),
       MAX(GP.END_DATE)
  FROM GL_PERIODS GP,
       GL_LEDGERS LED
 WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
   AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
   AND LED.LEDGER_ID = pn_ledger_id --parameter: pn_ledger_id
   AND GP.PERIOD_YEAR = TO_NUMBER(pv_accounting_year); --parameter: pv_accounting_year

Line: 473

SELECT ppf.employee_number EMPLOYEE_ID,
       ppf.full_name EMPLOYEE_NAME,
       ppf.person_id PERSON_ID,
       --paf.effective_start_date EFFECTIVE_START_DATE, --update for bug 9742065
       ppf.effective_start_date EFFECTIVE_START_DATE,
       NULL DOCUMENT_TYPE,
       NULL DOCUMENT_NUMBER,
       ppf.sex GENDER,
       to_char(ppf.date_of_birth,
               'YYYYMMDD') DATE_OF_BIRTH,
       paf.organization_id DEPARTMENT_ID,
       to_char(pos.date_start,
               'YYYYMMDD') HIRE_DATE,
       to_char(pos.actual_termination_date,
               'YYYYMMDD') TERMINATION_DATE
  FROM per_people_f ppf,
       per_assignments_f paf,
       per_periods_of_service pos
 WHERE ppf.business_group_id = paf.business_group_id
   AND pos.period_of_service_id = paf.period_of_service_id
   AND paf.person_id = ppf.person_id
   AND (pd_acc_end_date BETWEEN paf.effective_start_date AND
       paf.effective_end_date OR
       (pos.final_process_date BETWEEN pd_acc_start_date AND
       pd_acc_end_date AND
       pos.final_process_date BETWEEN paf.effective_start_date AND
       paf.effective_end_date))
   AND (
        (nvl(pos.final_process_date,
             pd_acc_end_date) >= pd_acc_end_date AND
       pd_acc_end_date BETWEEN ppf.effective_start_date AND
       ppf.effective_end_date) OR
       (pos.final_process_date BETWEEN pd_acc_start_date AND
       pd_acc_end_date AND
       pos.final_process_date BETWEEN ppf.effective_start_date AND
       ppf.effective_end_date)
       --bug# 10066598, employment temination and rehire
       AND pos.period_of_service_id =
             ( SELECT MAX(ppos.period_of_service_id)
                 FROM per_periods_of_service ppos
                WHERE ppos.person_id = ppf.person_id)
       )
   AND ppf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
   AND paf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');