eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JA_CN_PS_HRMS_EXPORT_PKG SQL Statements

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

Line: 61


SELECT
  meaning
FROM
  fnd_lookup_values
WHERE lookup_type = pv_type
  AND enabled_flag = 'Y'
  AND lookup_code = pv_code
  AND language = userenv('lang')
ORDER BY lookup_code;

Line: 127


SELECT
  '1'
FROM
  per_all_people_f
WHERE person_id = pn_person_id
  AND pd_date BETWEEN effective_start_date AND effective_end_date;

Line: 139


SELECT
  national_identifier
FROM
  per_all_people_f
WHERE person_id = pn_person_id
  AND pd_date BETWEEN effective_start_date AND effective_end_date;

Line: 149


SELECT
  pei_information1 pei_info1
, pei_information2 pei_info2
FROM
  per_people_extra_info
WHERE person_id = pn_person_id
  AND information_type = 'PER_PASS_INFO_CN'
  AND pei_information_category = 'PER_PASS_INFO_CN'
ORDER BY pei_info1;

Line: 162


SELECT
  pei_information2
FROM
  per_people_extra_info
WHERE person_id = pn_person_id
  AND information_type = 'PER_PASSPORT_INFO_CN'
  AND pei_information_category = 'PER_PASSPORT_INFO_CN';

Line: 280


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      = pn_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: 338


SELECT
  analysis_criteria_id
FROM
  per_person_analyses
WHERE person_id = pn_person_id
  AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
  AND pd_date BETWEEN date_from AND nvl(date_to,to_date('31-12-4712','dd-mm-yyyy'))
  AND id_flex_num = (SELECT id_flex_num
                       FROM fnd_id_flex_structures_vl
                      WHERE id_flex_code='PEA'
                        AND id_flex_structure_code = 'PER_JOB_CLASS_INFO_CN'
                     )
ORDER BY date_from DESC;

Line: 356


SELECT
  segment1 cad_job_class
FROM
  per_analysis_criteria
WHERE analysis_criteria_id = p_anal_cri_id
  AND enabled_flag = 'Y';

Line: 411


SELECT
  analysis_criteria_id
FROM
  per_person_analyses
WHERE person_id = pn_person_id
  AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
  AND pd_date BETWEEN date_from AND nvl(date_to,to_date('31-12-4712','dd-mm-yyyy'))
  AND id_flex_num = (SELECT id_flex_num
                       FROM fnd_id_flex_structures_vl
                      WHERE id_flex_code='PEA'
                        AND id_flex_structure_code = 'PER_TECH_PROF_POST_CN'
                     )
ORDER BY date_from DESC;

Line: 427


SELECT
  segment1 tech_post_detail
FROM
  PER_ANALYSIS_CRITERIA
WHERE ANALYSIS_CRITERIA_ID = p_anal_cri_id
  AND enabled_flag = 'Y';

Line: 492


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
   --parameter: pv_accounting_year
  AND gp.period_year = TO_NUMBER(pv_accounting_year);

Line: 507


SELECT
  orgtl.name          department_name
, org.organization_id 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 <= ld_acc_end_date
  AND nvl( date_to, ld_acc_end_date) >= ld_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: 673


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
  --parameter: pv_accounting_year
  AND gp.period_year = TO_NUMBER(pv_accounting_year);

Line: 687


SELECT
  ppf.employee_number employee_id
, ppf.full_name employee_name
, ppf.person_id person_id
, ppf.effective_start_date effective_start_date
, ppf.sex gender
, ppf.per_information17 race -- is it flex field. need change or not.
, ppf.per_information6 education_level
, ppf.per_information20 degree
, TO_CHAR(ppf.date_of_birth ,'YYYYMMDD') date_of_birth
, paf.employee_category employee_category
, paf.organization_id department_id
, TO_CHAR(pos.date_start ,'YYYYMMDD') date_of_hire
, TO_CHAR(pos.actual_termination_date ,'YYYYMMDD') date_of_termination
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)
        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');

Line: 982


SELECT
--bug#10120576 added the DISTINCT
  DISTINCT paf.employee_category employee_category
FROM
  per_assignments_f paf
WHERE paf.business_group_id = FND_PROFILE.value('PER_BUSINESS_GROUP_ID')
  AND  ld_acc_end_date
       BETWEEN paf.effective_start_date AND paf.effective_end_date
  AND  EXISTS (SELECT
                 1
               FROM
                 hr_organization_units hou
		           , hr_soft_coding_keyflex scl
               WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
		             AND scl.segment1 = hou.organization_id
		             AND paf.business_group_id = FND_PROFILE.value('PER_BUSINESS_GROUP_ID')
                )
  AND paf.employee_category IS NOT NULL;

Line: 1001


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
  AND gp.period_year = TO_NUMBER(pv_accounting_year);