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.PQH_PQIPED6_XMLP_PKG SQL Statements

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

Line: 109


                                  SELECT 1 line,
                                    hl.lookup_code job_category_name,
pqh_salary_class_intervals_pkg.get_job_sal_interval(pqh_employment_category.identify_empl_category(ass.employment_category,
cp_fr,   cp_ft,   cp_pr,   cp_pt),   hl.lookup_code,   nvl(ppp.proposed_salary_n,   0) * ppb.pay_annualization_factor) salary_range,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   '1',   decode(peo.sex,   'M',   1,   NULL),   NULL)) nrmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   '1',   decode(peo.sex,   'F',   1,   NULL),   NULL)) nrwmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '2',   decode(peo.sex,   'M',   1,   NULL),   NULL)))) bnhmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '2',   decode(peo.sex,   'F',   1,   NULL),   NULL)))) bnhwmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '6',   decode(peo.sex,   'M',   1,   NULL),   NULL)))) am_almen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '6',   decode(peo.sex,   'F',   1,   NULL),   NULL)))) am_alwmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '4',   decode(peo.sex,   'M',   1,   NULL),   '5',   decode(peo.sex,   'M',   1,   NULL),   NULL)))) a_pmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '4',   decode(peo.sex,   'F',   1,   NULL),   '5',   decode(peo.sex,   'F',   1,   NULL),   NULL)))) a_pwmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '3',   decode(peo.sex,   'M',   1,   NULL),   '9',   decode(peo.sex,   'M',   1,   NULL),   NULL)))) hmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '3',   decode(peo.sex,   'F',   1,   NULL),   '9',   decode(peo.sex,   'F',   1,   NULL),   NULL)))) hwmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '1',   decode(peo.sex,   'M',   1,   NULL),   NULL)))) wnhmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '1',   decode(peo.sex,   'F',   1,   NULL),   NULL)))) wnhwmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   NULL,   decode(peo.sex,   'M',   1,   NULL),   NULL)),   NULL)) urmen,
COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   NULL,   decode(peo.sex,   'F',   1,   NULL),   NULL)),   NULL)) urwmen
FROM per_all_people_f peo,
  per_all_assignments_f ass,
  per_assignment_status_types ast,
  per_jobs job,
  per_pay_proposals ppp,
  per_pay_bases ppb,
  hr_lookups hl
WHERE peo.person_id = ass.person_id
 AND peo.current_employee_flag = 'Y'
 AND hl.lookup_code = job.job_information8
 AND job.job_information8 NOT IN('1',   '2',   '3',   '4')
 AND pqh_employment_category.identify_empl_category(ass.employment_category,   cp_fr,   cp_ft,   cp_pr,   cp_pt) IN('FR')
 AND ppp.change_date =
  (SELECT MAX(change_date)
   FROM per_pay_proposals PRO
  WHERE ppp.assignment_id = PRO.ASSIGNMENT_ID
  AND PRO.CHANGE_DATE <= P_REPORT_DATE
  AND PRO.APPROVED = 'Y' )
  AND ass.pay_basis_id = ppb.pay_basis_id
   AND ass.assignment_id = ppp.assignment_id
   AND nvl(ppp.proposed_salary_n,    0) * ppb.pay_annualization_factor > 0
   AND hl.lookup_type = 'US_IPEDS_JOB_CATEGORIES'
   AND job.job_information_category = 'US'
   AND p_report_date BETWEEN peo.effective_start_date
   AND peo.effective_end_date
   AND p_report_date BETWEEN ass.effective_start_date
   AND ass.effective_end_date
   AND ass.primary_flag = 'Y'
   AND ass.assignment_status_type_id = ast.assignment_status_type_id
   AND ast.per_system_status <> 'TERM_ASSIGN'
   AND ass.job_id = job.job_id
   AND ass.assignment_type = 'E'
   AND ass.organization_id IN
    (SELECT org.organization_id
     FROM hr_all_organization_units org
     WHERE business_group_id = p_business_group_id
     AND pqh_inst_type_pkg.get_inst_type(org.organization_id) = 'NON-MED')
  GROUP BY
  pqh_salary_class_intervals_pkg.get_job_sal_interval(pqh_employment_category.identify_empl_category(ass.employment_category,
  cp_fr,    cp_ft,    cp_pr,    cp_pt),    hl.lookup_code,    nvl(ppp.proposed_salary_n,    0) * ppb.pay_annualization_factor),
     hl.lookup_code;

Line: 169


SELECT COUNT(decode(pei.pei_information5,    '2',    decode(peo.sex,    'M',    1,    NULL),    NULL)) bnhmen,
COUNT(decode(pei.pei_information5,    '2',    decode(peo.sex,    'F',    1,    NULL),    NULL)) bnhwmen,
COUNT(decode(pei.pei_information5,    '6',    decode(peo.sex,    'M',    1,    NULL),    NULL)) am_almen,
COUNT(decode(pei.pei_information5,    '6',    decode(peo.sex,    'F',    1,    NULL),    NULL)) am_alwmen,
COUNT(decode(pei.pei_information5,    '4',    decode(peo.sex,    'M',    1,    NULL),    '5',    decode(peo.sex,    'M',    1,    NULL),    NULL)) a_pmen,
COUNT(decode(pei.pei_information5,    '4',    decode(peo.sex,    'F',    1,    NULL),    '5',    decode(peo.sex,    'F',    1,    NULL),    NULL)) a_pwmen,
COUNT(decode(pei.pei_information5,    '3',    decode(peo.sex,    'M',    1,    NULL),    '9',    decode(peo.sex,    'M',    1,    NULL),    NULL)) hmen,
COUNT(decode(pei.pei_information5,    '3',    decode(peo.sex,    'F',    1,    NULL),    '9',    decode(peo.sex,    'F',    1,    NULL),    NULL)) hwmen,
COUNT(decode(pei.pei_information5,    '1',    decode(peo.sex,    'M',    1,    NULL),    NULL)) wnhmen,
COUNT(decode(pei.pei_information5,    '1',    decode(peo.sex,    'F',    1,    NULL),    NULL)) wnhwmen,
COUNT(decode(pei.pei_information5,    NULL,    decode(peo.sex,    'M',    1,    NULL),    NULL)) urmen,
COUNT(decode(pei.pei_information5,    NULL,    decode(peo.sex,    'F',    1,    NULL),    NULL)) urwmen
  FROM per_all_people_f peo,
    per_all_assignments_f ass,
    per_assignment_status_types ast,
    per_jobs job,
    per_pay_proposals ppp,
    per_pay_bases ppb,
    hr_lookups hl,
    per_people_extra_info pei
  WHERE peo.person_id = ass.person_id
  AND peo.current_employee_flag = 'Y'
  AND peo.per_information1 = '13'
  AND peo.person_id = pei.person_id(+)
  AND(pei.information_type = 'PER_US_ADDL_ETHNIC_CAT' OR(pei.information_type <> 'PER_US_ADDL_ETHNIC_CAT'
  AND NOT EXISTS
   (SELECT 1
    FROM per_people_extra_info pei2
    WHERE pei2.information_type = 'PER_US_ADDL_ETHNIC_CAT'
    AND pei2.person_id = pei.person_id)
    AND pei.person_extra_info_id =
     (SELECT MAX(pei1.person_extra_info_id)
      FROM per_people_extra_info pei1
      WHERE pei1.person_id = pei.person_id))
   OR(NOT EXISTS
     (SELECT person_extra_info_id
      FROM per_people_extra_info pei3
      WHERE pei3.person_id = pei.person_id))
   )
 AND hl.lookup_code = job.job_information8
  AND job.job_information8 NOT IN('1',    '2',    '3',    '4')
  AND pqh_employment_category.identify_empl_category(ass.employment_category,    cp_fr,    cp_ft,    cp_pr,    cp_pt) IN('FR')
  AND ppp.change_date =
   (SELECT MAX(change_date)
    FROM per_pay_proposals PRO
   WHERE ppp.assignment_id = PRO.ASSIGNMENT_ID
   AND PRO.CHANGE_DATE <= P_REPORT_DATE
   AND PRO.APPROVED = 'Y' )
   AND ass.pay_basis_id = ppb.pay_basis_id
    AND ass.assignment_id = ppp.assignment_id
    AND nvl(ppp.proposed_salary_n,    0) * ppb.pay_annualization_factor > 0
    AND hl.lookup_type = 'US_IPEDS_JOB_CATEGORIES'
    AND job.job_information_category = 'US'
    AND p_report_date BETWEEN peo.effective_start_date
    AND peo.effective_end_date
    AND p_report_date BETWEEN ass.effective_start_date
    AND ass.effective_end_date
    AND ass.primary_flag = 'Y'
    AND ass.assignment_status_type_id = ast.assignment_status_type_id
    AND ast.per_system_status <> 'TERM_ASSIGN'
    AND ass.job_id = job.job_id
    AND ass.assignment_type = 'E'
    AND ass.organization_id IN
     (SELECT org.organization_id
     FROM hr_all_organization_units org
     WHERE business_group_id = p_business_group_id
     AND pqh_inst_type_pkg.get_inst_type(org.organization_id) = 'NON-MED')
  AND pqh_salary_class_intervals_pkg.get_job_sal_interval(pqh_employment_category.identify_empl_category(ass.employment_category,    cp_fr,    cp_ft,    cp_pr,    cp_pt),    hl.lookup_code,
	 nvl(ppp.proposed_salary_n,    0) * ppb.pay_annualization_factor) = c_salary_range
   AND hl.lookup_code = c_lookup_code;

Line: 289


   INSERT
    INTO pay_us_rpt_totals(session_id,    attribute1,    attribute2,    value1,    value2,    value3,    value4,
	   value5,    value6,    value7,    value8,    value9,    value10,    value11,    value12,    value13,    value14,    value15,    value16,    value17,    value18)
    VALUES(userenv('sessionid'),    'IPED6',    salary_range,    line,    sc,
	   l_nr_men,    l_nr_wmen,    l_bnh_men,    l_bnh_wmen,    l_amai_men,    l_amai_wmen,    l_ap_men,    l_ap_wmen,    l_h_men,    l_h_wmen,    l_wnh_men,
	   l_wnh_wmen,    l_ur_men,    l_ur_wmen,    l_tot_men,    l_tot_wmen);

Line: 304


EXECUTE IMMEDIATE 'DELETE FROM pay_us_rpt_totals
                    WHERE attribute1 = ''IPED6''';