[Home] [Help]
PACKAGE BODY: APPS.PQH_PQHEEO4_XMLP_PKG
Source
4 L_QUERY_TEXT VARCHAR2(2000);
1 PACKAGE BODY PQH_PQHEEO4_XMLP_PKG AS
2 /* $Header: PQHEEO4B.pls 120.1 2007/12/07 06:47:50 vjaganat noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
5 BEGIN
6 --HR_STANDARD.EVENT('BEFORE REPORT');
7 C_REPORT_TYPE := P_REPORT_YEAR || ' EEO-4 REPORT';
8 DECLARE
9 L_FR VARCHAR2(2000);
10 L_FT VARCHAR2(2000);
11 L_PR VARCHAR2(2000);
12 L_PT VARCHAR2(2000);
13 L_PROFILE_OPTION VARCHAR2(10);
17 L_DYNAMIC_WHERE VARCHAR2(4000) := ' ''XX'' ';
14 L_FUNCTION_CODE VARCHAR2(20);
15 L_FUNCTION_NAME VARCHAR2(240);
16 L_FUNCTION_COUNT NUMBER;
18 L_REPORT_COMMAND VARCHAR2(500);
19 L_REPORT_DATE DATE := TO_DATE(P_REPORT_DAY_MONTH || '-' || P_REPORT_YEAR
20 ,'DD-MM-YYYY');
21 L_FT_EMP_COUNT NUMBER;
22 SOURCE_CURSOR INTEGER;
23 ROWS_PROCESSED INTEGER;
24 SELECT_CLAUSE VARCHAR2(2000) := 'count(peo.person_id) cons_total_category_emps,
25 count(decode(peo.per_information1,''1'',
26 decode(peo.sex,''M'',1,null),null)) no_cons_wmale_emps,
27 count(decode(peo.per_information1,''2'',
28 decode(peo.sex,''M'',1,null),null)) no_cons_bmale_emps,
29 count(decode(peo.per_information1,''3'',
30 decode(peo.sex,''M'',1,null),null)) no_cons_hmale_emps,
31 count(decode(peo.per_information1,''4'',
32 decode(peo.sex,''M'',1,null),''5'',
33 decode(peo.sex,''M'',1,null),null)) no_cons_amale_emps,
34 count(decode(peo.per_information1,''6'',
35 decode(peo.sex,''M'',1,null),''7'',
36 decode(peo.sex,''M'',1,null),null)) no_cons_imale_emps,
37 count(decode(peo.per_information1,''1'',
38 decode(peo.sex,''F'',1,null),null)) no_cons_wfemale_emps,
39 count(decode(peo.per_information1,''2'',
40 decode(peo.sex,''F'',1,null),null)) no_cons_bfemale_emps,
41 count(decode(peo.per_information1,''3'',
42 decode(peo.sex,''F'',1,null),null)) no_cons_hfemale_emps,
43 count(decode(peo.per_information1,''4'',
44 decode(peo.sex,''F'',1,null),''5'',
45 decode(peo.sex,''F'',1,null),null)) no_cons_afemale_emps,
46 count(decode(peo.per_information1,''6'',
47 decode(peo.sex,''F'',1,null),''7'',
48 decode(peo.sex,''F'',1,null),null)) no_cons_ifemale_emps ';
49 FROM_WHERE_CLAUSE VARCHAR2(10000) := '
50 FROM per_all_people_f peo,
51 per_all_assignments_f ass,
52 per_assignment_status_types ast1,
53 per_pay_proposals ppp,
54 per_jobs job,
55 per_pay_bases ppb,
56 hr_lookups hl
57 WHERE peo.person_id = ass.person_id
58 AND ass.pay_basis_id = ppb.pay_basis_id
59 AND ass.assignment_id = ppp.assignment_id
60 AND hl.lookup_code = job.job_information1
61 AND job.job_information1 IS NOT NULL
62 AND job.job_information_category = ''US''
63 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
64 AND ass.job_id = job.job_id
65 AND ass.primary_flag = ''Y''
66 AND ppp.change_date = (SELECT MAX(change_date)
67 FROM per_pay_proposals pro
68 WHERE ppp.assignment_id = pro.assignment_id
69 AND pro.change_date <= ''' || TO_CHAR(L_REPORT_DATE) || '''
70 AND pro.approved = ''Y'' )
71 AND ass.organization_id IN (
72 SELECT organization_id
73 FROM hr_all_organization_units
74 WHERE business_group_id = ' || TO_CHAR(P_BUSINESS_GROUP_ID) || '
75 AND SYSDATE BETWEEN date_from AND NVL(date_to,SYSDATE) )
76 AND ass.assignment_status_type_id = ast1.assignment_status_type_id
77 AND peo.current_employee_flag = ''Y''
78 AND ass.assignment_type = ''E''';
79 L_FT_EFFECTIVE_DATES VARCHAR2(1000) := '
80 AND ''' || TO_CHAR(L_REPORT_DATE) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
81 AND ''' || TO_CHAR(L_REPORT_DATE) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
82 AND ast1.per_system_status <> ''TERM_ASSIGN''';
83 L_NH_EFFECTIVE_DATES VARCHAR2(1000) := 'AND (SELECT date_start
84 FROM per_periods_of_service
85 WHERE period_of_service_id = ass.period_of_service_id) BETWEEN ''' || TO_CHAR(ADD_MONTHS(L_REPORT_DATE
86 ,-12) + 1) || ''' AND ''' || TO_CHAR(L_REPORT_DATE) || ''' ';
87 L_ROW_COUNT NUMBER := 0;
88 L_FRC VARCHAR2(2000);
89 BEGIN
90 PQH_EMPLOYMENT_CATEGORY.FETCH_EMPL_CATEGORIES(P_BUSINESS_GROUP_ID
91 ,L_FR
92 ,L_FT
93 ,L_PR
94 ,L_PT);
95 L_FRC := '(' || REPLACE(L_FR
96 ,','
97 ,''',''') || ')';
98 SOURCE_CURSOR := DBMS_SQL.OPEN_CURSOR;
99 L_QUERY_TEXT := 'Select count(1) l_ft_emp_count' || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || 'AND ass.employment_category in ' || L_FRC;
100 DBMS_SQL.PARSE(SOURCE_CURSOR
101 ,L_QUERY_TEXT
102 ,2);
103 DBMS_SQL.DEFINE_COLUMN(SOURCE_CURSOR
104 ,1
105 ,L_FT_EMP_COUNT);
106 ROWS_PROCESSED := DBMS_SQL.EXECUTE(SOURCE_CURSOR);
107 IF DBMS_SQL.FETCH_ROWS(SOURCE_CURSOR) > 0 THEN
108 DBMS_SQL.COLUMN_VALUE(SOURCE_CURSOR
109 ,1
110 ,L_FT_EMP_COUNT);
111 END IF;
112 DBMS_SQL.CLOSE_CURSOR(SOURCE_CURSOR);
113 SOURCE_CURSOR := DBMS_SQL.OPEN_CURSOR;
114 L_QUERY_TEXT := 'Select job.job_information7 l_function_code,
115 count(1) l_function_count ' || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || ' group by job.job_information7 ';
116 DBMS_SQL.PARSE(SOURCE_CURSOR
117 ,L_QUERY_TEXT
121 ,L_FUNCTION_CODE
118 ,2);
119 DBMS_SQL.DEFINE_COLUMN_CHAR(SOURCE_CURSOR
120 ,1
122 ,10);
123 DBMS_SQL.DEFINE_COLUMN(SOURCE_CURSOR
124 ,2
125 ,L_FUNCTION_COUNT);
126 ROWS_PROCESSED := DBMS_SQL.EXECUTE(SOURCE_CURSOR);
127 LOOP
128 IF DBMS_SQL.FETCH_ROWS(SOURCE_CURSOR) > 0 THEN
129 DBMS_SQL.COLUMN_VALUE_CHAR(SOURCE_CURSOR
130 ,1
131 ,L_FUNCTION_CODE);
132 DBMS_SQL.COLUMN_VALUE(SOURCE_CURSOR
133 ,2
134 ,L_FUNCTION_COUNT);
135 L_FUNCTION_CODE := RTRIM(L_FUNCTION_CODE);
136 IF (L_FT_EMP_COUNT > P_BPT_FT_EMP_COUNT AND L_ROW_COUNT <= 14) OR (L_FT_EMP_COUNT < P_BPT_FT_EMP_COUNT AND L_FUNCTION_COUNT > P_BPT_EMP_COUNT_FUNC) THEN
137 L_DYNAMIC_WHERE := L_DYNAMIC_WHERE || ',''' || L_FUNCTION_CODE || ''' ';
138 END IF;
139 IF (L_FT_EMP_COUNT > P_BPT_FT_EMP_COUNT AND L_ROW_COUNT > 14) OR (L_FT_EMP_COUNT < P_BPT_FT_EMP_COUNT) THEN
140 IF FUNCTION_DESC IS NULL AND L_FUNCTION_CODE < 'XX' THEN
141 FUNCTION_DESC := L_FUNCTION_CODE;
142 ELSE
143 FUNCTION_DESC := FUNCTION_DESC || ' ' || L_FUNCTION_CODE;
144 END IF;
145 END IF;
146 L_ROW_COUNT := L_ROW_COUNT + 1;
147 ELSE
148 EXIT;
149 END IF;
150 END LOOP;
151 DBMS_SQL.CLOSE_CURSOR(SOURCE_CURSOR);
152 IF (L_FT_EMP_COUNT > P_BPT_FT_EMP_COUNT) THEN
153 P_EEO4_QUERY := 'SELECT job.job_information7 Job_function_code,
154 PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
155 PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
156 ''FR'', ''1. FULL-TIME EMPLOYEES'',
157 ''2. OTHER THAN FULL-TIME EMPLOYEES'')) CF_TOTAL_TITLE,
158 PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(job.job_information7) CF_set_function_desc,
159 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
160 ''FR'', ''1. FULL-TIME EMPLOYEES'',
161 ''2. OTHER THAN FULL-TIME EMPLOYEES'') Employment_Category,
162 hl.lookup_code job_category_code,
163 hl.meaning Job_category_name,
164 pqh_salary_range_pkg.get_salary_range(
165 pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
166 NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor) Salary_range, ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || '
167 AND job.job_information7 IN (' || L_DYNAMIC_WHERE || ')
168 GROUP BY job.job_information7,
169 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
170 ''FR'', ''1. FULL-TIME EMPLOYEES'',
171 ''2. OTHER THAN FULL-TIME EMPLOYEES''),
172 hl.lookup_code ,
173 hl.meaning ,
174 pqh_salary_range_pkg.get_salary_range(
175 pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
176 NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)
177 UNION
178 SELECT job.job_information7,
179 PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
180 PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'') CF_TOTAL_TITLE,
181 PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(job.job_information7) CF_set_function_desc,
182 ''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'',
183 hl.lookup_code job_category_code,
184 hl.meaning Job_category_name,
185 '' '', ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_NH_EFFECTIVE_DATES || '
186 AND ass.employment_category in ' || L_FRC || '
187 AND job.job_information7 IN (' || L_DYNAMIC_WHERE || ')
188 GROUP BY job.job_information7,
189 hl.lookup_code,
190 hl.meaning
191 UNION
192 SELECT ''XX'',
193 PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
194 PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
195 ''FR'', ''1. FULL-TIME EMPLOYEES'',
196 ''2. OTHER THAN FULL-TIME EMPLOYEES'')) CF_TOTAL_TITLE,
197 PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(''XX'') CF_set_function_desc,
198 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
202 hl.meaning ,
199 ''FR'', ''1. FULL-TIME EMPLOYEES'',
200 ''2. OTHER THAN FULL-TIME EMPLOYEES'') ,
201 hl.lookup_code ,
203 pqh_salary_range_pkg.get_salary_range(
204 pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
205 NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor), ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || '
206 AND job.job_information7 NOT IN (' || L_DYNAMIC_WHERE || ')
207 GROUP BY
208 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
209 ''FR'', ''1. FULL-TIME EMPLOYEES'',
210 ''2. OTHER THAN FULL-TIME EMPLOYEES''),
211 hl.lookup_code ,
212 hl.meaning ,
213 pqh_salary_range_pkg.get_salary_range(
214 pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
218 PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
215 NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)
216 UNION
217 SELECT ''XX'',
219 PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'') CF_TOTAL_TITLE,
220 PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(''XX'') CF_set_function_desc,
221 ''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'',
222 hl.lookup_code job_category_code,
223 hl.meaning Job_category_name,
224 '' '', ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_NH_EFFECTIVE_DATES || '
225 AND ass.employment_category in ' || L_FRC || '
226 AND job.job_information7 NOT IN (' || L_DYNAMIC_WHERE || ')
227 GROUP BY
228 hl.lookup_code,
229 hl.meaning ';
230 ELSE
231 P_EEO4_QUERY := 'SELECT job.job_information7 Job_function_code,
232 PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
233 PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
234 ''FR'', ''1. FULL-TIME EMPLOYEES'',
235 ''2. OTHER THAN FULL-TIME EMPLOYEES'')) CF_TOTAL_TITLE,
236 PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(job.job_information7) CF_set_function_desc,
237 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
238 ''FR'', ''1. FULL-TIME EMPLOYEES'',
239 ''2. OTHER THAN FULL-TIME EMPLOYEES'') Employment_Category,
240 hl.lookup_code job_category_code,
241 hl.meaning Job_category_name,
242 pqh_salary_range_pkg.get_salary_range(
243 pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
244 NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor) Salary_range, ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || '
245 AND job.job_information7 IN (' || L_DYNAMIC_WHERE || ')
246 GROUP BY job.job_information7,
247 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
248 ''FR'', ''1. FULL-TIME EMPLOYEES'',
249 ''2. OTHER THAN FULL-TIME EMPLOYEES''),
250 hl.lookup_code ,
251 hl.meaning ,
252 pqh_salary_range_pkg.get_salary_range(
253 pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
254 NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)
255 UNION
256 SELECT job.job_information7,
257 PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
261 hl.lookup_code job_category_code,
258 PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'') CF_TOTAL_TITLE,
259 PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(job.job_information7) CF_set_function_desc,
260 ''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'',
262 hl.meaning Job_category_name,
263 '' '', ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_NH_EFFECTIVE_DATES || '
264 AND job.job_information7 IN (' || L_DYNAMIC_WHERE || ')
265 AND ass.employment_category in ' || L_FRC || '
266 GROUP BY job.job_information7,
267 hl.lookup_code,
268 hl.meaning
269 UNION
270 SELECT ''XX'' ,
271 PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
272 PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
273 ''FR'', ''1. FULL-TIME EMPLOYEES'',
274 ''2. OTHER THAN FULL-TIME EMPLOYEES'')) CF_TOTAL_TITLE,
275 PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(''XX'') CF_set_function_desc,
276 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
277 ''FR'', ''1. FULL-TIME EMPLOYEES'',
278 ''2. OTHER THAN FULL-TIME EMPLOYEES'') ,
279 hl.lookup_code ,
280 hl.meaning ,
281 pqh_salary_range_pkg.get_salary_range(
282 pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
283 NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor) , ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_FT_EFFECTIVE_DATES || '
284 GROUP BY
285 DECODE(pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
286 ''FR'', ''1. FULL-TIME EMPLOYEES'',
287 ''2. OTHER THAN FULL-TIME EMPLOYEES''),
288 hl.lookup_code ,
289 hl.meaning ,
290 pqh_salary_range_pkg.get_salary_range(
291 pqh_employment_category.identify_empl_category(ass.employment_category,' || L_FR || ',' || L_FT || ',' || L_PR || ',' || L_PT || '),
292 NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)
293 UNION
294 SELECT ''XX'',
295 PQH_PQHEEO4_XMLP_PKG.CP_1_P CP_1,
296 PQH_PQHEEO4_XMLP_PKG.CF_TOTAL_TITLEFORMULA0005(''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'') CF_TOTAL_TITLE,
297 PQH_PQHEEO4_XMLP_PKG.CF_SET_FUNCTION_DESCFORMULA(''XX'') CF_set_function_desc,
298 ''3. NEW HIRE DURING FISCAL YEAR - PERMANENT FULL TIME ONLY'',
299 hl.lookup_code job_category_code,
300 hl.meaning Job_category_name,
301 '' '', ' || SELECT_CLAUSE || FROM_WHERE_CLAUSE || L_NH_EFFECTIVE_DATES || '
302 AND ass.employment_category in ' || L_FRC || '
303 GROUP BY
304 hl.lookup_code,
305 hl.meaning ';
306 END IF;
307 END;
308 RETURN TRUE;
309 END BEFOREREPORT;
310
311 FUNCTION CF_TOTAL_TITLEFORMULA0005(EMPLOYMENT_CATEGORY IN VARCHAR2) RETURN CHAR IS
312 L_TOTAL_TITLE VARCHAR2(200) := 'TOTAL OTHERS';
313 L_EMP_CATEGORY VARCHAR2(1) := SUBSTR(EMPLOYMENT_CATEGORY
314 ,4
315 ,1);
316 BEGIN
317 IF L_EMP_CATEGORY = 'F' THEN
318 L_TOTAL_TITLE := 'TOTAL FULL-TIME';
319 ELSIF L_EMP_CATEGORY = 'O' THEN
320 L_TOTAL_TITLE := 'TOTAL OTHER THAN F-T';
321 ELSIF L_EMP_CATEGORY = 'N' THEN
322 L_TOTAL_TITLE := 'TOTAL NEW HIRES';
323 END IF;
324 RETURN L_TOTAL_TITLE;
325 END CF_TOTAL_TITLEFORMULA0005;
326
327 FUNCTION CF_SET_FUNCTION_DESCFORMULA(JOB_FUNCTION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
328 CURSOR FUNC_DESC_CUR IS
329 SELECT
330 MEANING
331 FROM
332 HR_LOOKUPS
333 WHERE LOOKUP_TYPE = 'US_EEO4_JOB_FUNCTIONS'
334 AND LOOKUP_CODE = JOB_FUNCTION_CODE;
335 L_FUNCTION_DESC VARCHAR2(240);
336 BEGIN
337 IF JOB_FUNCTION_CODE < 'XX' THEN
338 OPEN FUNC_DESC_CUR;
339 FETCH FUNC_DESC_CUR
340 INTO
341 L_FUNCTION_DESC;
342 CLOSE FUNC_DESC_CUR;
343 RETURN (JOB_FUNCTION_CODE || ' ' || L_FUNCTION_DESC);
344 ELSE
345 RETURN (FUNCTION_DESC);
346 END IF;
347 END CF_SET_FUNCTION_DESCFORMULA;
348
349 FUNCTION AFTERREPORT RETURN BOOLEAN IS
350 BEGIN
351 --HR_STANDARD.EVENT('AFTER REPORT');
352 RETURN (TRUE);
353 END AFTERREPORT;
354
355 FUNCTION CP_1_P RETURN NUMBER IS
356 BEGIN
357 RETURN CP_1;
358 END CP_1_P;
359
360 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
361 BEGIN
362 RETURN C_BUSINESS_GROUP_NAME;
366 BEGIN
363 END C_BUSINESS_GROUP_NAME_P;
364
365 FUNCTION C_REPORT_TYPE_P RETURN VARCHAR2 IS
367 RETURN C_REPORT_TYPE;
368 END C_REPORT_TYPE_P;
369
370 FUNCTION C_ORGANIZATION_HIERARCHY_P RETURN VARCHAR2 IS
371 BEGIN
372 RETURN C_ORGANIZATION_HIERARCHY;
373 END C_ORGANIZATION_HIERARCHY_P;
374
375 FUNCTION C_EEO1_ORGANIZATION_P RETURN VARCHAR2 IS
376 BEGIN
377 RETURN C_EEO1_ORGANIZATION;
378 END C_EEO1_ORGANIZATION_P;
379
380 FUNCTION C_END_OF_TIME_P RETURN DATE IS
381 BEGIN
382 RETURN C_END_OF_TIME;
383 END C_END_OF_TIME_P;
384
385 END PQH_PQHEEO4_XMLP_PKG;