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

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

Line: 65


    g_select_clause VARCHAR2(10000);

Line: 76


    g_tmraces_select_clause VARCHAR2(10000);

Line: 313


  g_nh_sql := 'SELECT   hl.lookup_code  job_category_code,
                        hl.meaning	job_category_name,'
                   || g_select_clause || g_from_where_clause || g_nh_effective_dates
                   ||' AND job.job_information7 in (' || p_job_codes || ')'
                   ||' AND hl.lookup_code = :1 '
                   ||' AND  ass.employment_category in (''FR'')' || g_group_order_by;

Line: 322


  g_ft_emp_sql := 'SELECT hl.lookup_code      job_category_code,
                          hl.meaning	      job_category_name,'
                  ||g_select_clause || g_from_where_clause|| g_ft_effective_dates
                  ||' AND job.job_information7 in ( ' || p_job_codes || ' ) '
                  ||' AND hl.lookup_code = :1 '
                  || p_dynamic_where
                  ||' AND  ass.employment_category in (''FR'')'
                  || g_group_order_by;

Line: 333


  g_oft_sql := 'SELECT   hl.lookup_code	    job_category_code,
                         hl.meaning	    job_category_name,'
                      ||g_select_clause||g_from_where_clause||g_ft_effective_dates
                      ||' AND job.job_information7 in ( ' || p_job_codes || ' )'
                      ||' AND hl.lookup_code = :1 '
                      ||' AND  ass.employment_category NOT IN (''FR'')'
                      ||g_group_order_by;

Line: 345


  g_tmr_nh_sql := 'SELECT   hl.lookup_code  job_category_code,
                        hl.meaning	job_category_name,'
                   || g_tmraces_select_clause || g_tmraces_where_clause || g_nh_effective_dates
                   ||' AND job.job_information7 in (' || p_job_codes || ')'
                   ||' AND hl.lookup_code = :1 '
                   ||' AND  ass.employment_category in (''FR'')' || g_group_order_by;

Line: 354


  g_tmr_ft_emp_sql := 'SELECT hl.lookup_code      job_category_code,
                          hl.meaning	      job_category_name,'
                  || g_tmraces_select_clause || g_tmraces_where_clause|| g_ft_effective_dates
                  ||' AND job.job_information7 in ( ' || p_job_codes || ' ) '
                  ||' AND hl.lookup_code = :1 '
                  || p_dynamic_where
                  ||' AND  ass.employment_category in (''FR'')'
                  || g_group_order_by;

Line: 365


  g_tmr_oft_sql := 'SELECT   hl.lookup_code	    job_category_code,
                         hl.meaning	    job_category_name,'
                      || g_tmraces_select_clause || g_tmraces_where_clause ||g_ft_effective_dates
                      ||' AND job.job_information7 in ( ' || p_job_codes || ' )'
                      ||' AND hl.lookup_code = :1 '
                      ||' AND  ass.employment_category NOT IN (''FR'')'
                      ||g_group_order_by;

Line: 435


  SELECT lookup_code, meaning, description
  FROM hr_lookups
  WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
  ORDER BY lookup_code;

Line: 441


  SELECT meaning
  FROM hr_lookups
  WHERE lookup_type = 'US_EEO4_JOB_FUNCTIONS'
  AND lookup_code = l_lookup_code;

Line: 450


  g_select_clause := ' count(decode(peo.per_information1,''1'',decode(peo.sex,''M'',1,null),null)) no_cons_wmale_emps,
                                count(decode(peo.per_information1,''2'',decode(peo.sex,''M'',1,null),null)) no_cons_bmale_emps,
                                count(decode(peo.per_information1,''3'',decode(peo.sex,''M'',1,null),null)) no_cons_hmale_emps,
                                count(decode(peo.per_information1,''4'',decode(peo.sex,''M'',1,null),''5'',decode(peo.sex,''M'',1,null),null)) no_cons_amale_emps,
                                count(decode(peo.per_information1,''6'',decode(peo.sex,''M'',1,null),null)) no_cons_imale_emps,
                                count(decode(peo.per_information1,''1'',decode(peo.sex,''F'',1,null),null)) no_cons_wfemale_emps,
                                count(decode(peo.per_information1,''2'',decode(peo.sex,''F'',1,null),null)) no_cons_bfemale_emps,
                                count(decode(peo.per_information1,''3'',decode(peo.sex,''F'',1,null),null)) no_cons_hfemale_emps,
                                count(decode(peo.per_information1,''4'',decode(peo.sex,''F'',1,null),''5'',decode(peo.sex,''F'',1,null),null)) no_cons_afemale_emps,
                                count(decode(peo.per_information1,''6'',decode(peo.sex,''F'',1,null),null)) no_cons_ifemale_emps ';

Line: 477


                            AND	ppp.change_date	= ( SELECT  MAX(change_date)
                                                    FROM	per_pay_proposals  pro
                                                    WHERE	ppp.assignment_id = pro.assignment_id
                                                    AND	pro.change_date <= ''' || to_char(l_report_date) || '''
                                                    AND     pro.approved = ''Y'' )
                            AND ass.organization_id IN (SELECT	organization_id
                                                        FROM	hr_all_organization_units
                                                        WHERE	business_group_id = ' || p_business_group_id || '
                                                        AND	SYSDATE BETWEEN  date_from AND NVL(date_to,SYSDATE) )
                            AND	ass.assignment_status_type_id = past.assignment_status_type_id
                            AND peo.current_employee_flag = ''Y''
                            AND ass.assignment_type = ''E''';

Line: 491


  g_tmraces_select_clause := ' count(decode(pei.pei_information5,''1'',decode(peo.sex,''M'',1,null),null)) no_tmraces_wmale_emps,
                                              count(decode(pei.pei_information5,''2'',decode(peo.sex,''M'',1,null),null)) no_tmraces_bmale_emps,
                                              count(decode(pei.pei_information5,''3'',decode(peo.sex,''M'',1,null),''9'',decode(peo.sex,''M'',1,null),null)) no_tmraces_hmale_emps,
                                              count(decode(pei.pei_information5,''4'',decode(peo.sex,''M'',1,null),''5'',decode(peo.sex,''M'',1,null),null)) no_tmraces_amale_emps,
                                              count(decode(pei.pei_information5,''6'',decode(peo.sex,''M'',1,null),null)) no_tmraces_imale_emps,
                                              count(decode(pei.pei_information5,''1'',decode(peo.sex,''F'',1,null),null)) no_tmraces_wfemale_emps,
                                              count(decode(pei.pei_information5,''2'',decode(peo.sex,''F'',1,null),null)) no_tmraces_bfemale_emps,
                                              count(decode(pei.pei_information5,''3'',decode(peo.sex,''F'',1,null),''9'',decode(peo.sex,''F'',1,null),null)) no_tmraces_hfemale_emps,
                                              count(decode(pei.pei_information5,''4'',decode(peo.sex,''F'',1,null),''5'',decode(peo.sex,''F'',1,null),null)) no_tmraces_afemale_emps,
                                              count(decode(pei.pei_information5,''6'',decode(peo.sex,''F'',1,null),null)) no_tmraces_ifemale_emps';

Line: 524


                            AND	   ppp.change_date = ( SELECT  MAX(change_date)
                                                                            FROM	per_pay_proposals  pro
                                                                            WHERE	ppp.assignment_id = pro.assignment_id
                                                                             AND	pro.change_date <= ''' || to_char(l_report_date) || '''
                                                                             AND     pro.approved = ''Y'' )
                            AND ass.organization_id IN (SELECT	organization_id
                                                                          FROM	hr_all_organization_units
                                                                          WHERE	business_group_id = ' || p_business_group_id || '
                                                                           AND	SYSDATE BETWEEN  date_from AND NVL(date_to,SYSDATE) )
                            AND	ass.assignment_status_type_id = past.assignment_status_type_id
                            AND peo.current_employee_flag = ''Y''
                            AND ass.assignment_type = ''E''';

Line: 537


  l_query_text := 'select count(1) l_ft_emp_count ' || g_from_where_clause
                  || g_ft_effective_dates || ' AND  ass.employment_category in (''FR'')';

Line: 553


                                         AND (SELECT date_start
                                         FROM   per_periods_of_service
                                         WHERE  period_of_service_id = ass.period_of_service_id) BETWEEN ''' || to_char(add_months(l_report_date,   -12) + 1) || ''' AND ''' || to_char(l_report_date) || ''' ';

Line: 576


  l_query_text := 'Select job.job_information7  l_function_code,
                   count(1) l_function_count ' || g_from_where_clause || g_ft_effective_dates || ' group by job.job_information7 ';

Line: 874


  SELECT name from hr_organization_units
  WHERE organization_id = g_business_group_id
  AND business_group_id = g_business_group_id;

Line: 903


         SELECT NVL(hou.name,' ')                                             jurisdiction_name,
		NVL(hl.address_line_1,' ')||' '||NVL(hl.address_line_2,' ')
                ||' '||NVL(hl.address_line_3,' ') 	                      address,
		NVL(hl.town_or_city,' ')                                      town_or_city,
                NVL(hl.region_1,' ')                                          county,
                NVL(hl.region_2,' ')||' '||NVL(hl.postal_code,' ')	      state_zip
	FROM	 hr_all_organization_units hou,
		 hr_locations hl
	WHERE  hou.location_id = hl.location_id
	AND  hou.business_group_id = g_business_group_id
	AND  hou.organization_id = g_business_group_id;

Line: 917


	SELECT 	NVL(name,' ')                                             jurisdiction_name,
		NVL(address_line_1,' ')||' '||NVL(address_line_2,' ')
                ||' '||NVL(address_line_3,' ') 	                          address,
		NVL(town_or_city,' ')                                     town_or_city,
                NVL(region_1,' ')                                         county,
                NVL(region_2,' ')||' '||NVL(postal_code,' ')		  state_zip
        FROM hr_all_organization_units
        WHERE business_group_id = g_business_group_id
        and organization_id = g_business_group_id;

Line: 930


	SELECT 	NVL(org_information1,' ')         cert_officer_name,
		NVL(org_information2,' ')	  cert_officer_title,
		NVL(org_information3,' ')	  contact_name,
		NVL(org_information4,' ')	  contact_title,
		NVL(org_information5,' ')
                ||' '|| NVL(org_information6,' ') contact_address,
		NVL(org_information7,' ')
                ||' '|| NVL(org_information8,' ')
                ||' '|| NVL(org_information9,' ') contact_city_state_zip,
		NVL(org_information10,' ')	  contact_telephone,
		NVL(org_information12,' ')	  control_number,
		NVL(org_information15, ' ')       email,
		NVL(org_information14, ' ')       fax
	FROM	hr_organization_information
	WHERE	org_information_context	= 'EEO_REPORT'
	AND	organization_id		= g_business_group_id;

Line: 1072


  SELECT lookup_code, meaning, description
  FROM hr_lookups
  WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
  ORDER BY lookup_code;

Line: 1227


	      select pei_information5 from per_people_extra_info table
	   */

	   source_cursor := dbms_sql.open_cursor;

Line: 1336


  SELECT lookup_code, meaning, description
  FROM hr_lookups
  WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
  ORDER BY lookup_code;

Line: 1446


	      select pei_information5 from per_people_extra_info table
	   */

	   source_cursor := dbms_sql.open_cursor;

Line: 1553


  SELECT lookup_code, meaning, description
  FROM hr_lookups
  WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
  ORDER BY lookup_code;

Line: 1665


	      select pei_information5 from per_people_extra_info table
	   */

	   source_cursor := dbms_sql.open_cursor;

Line: 1777


  SELECT lookup_code, meaning, description
  FROM hr_lookups
  WHERE lookup_type = 'US_EEO4_JOB_CATEGORIES'
  ORDER BY lookup_code;