[Home] [Help]
PACKAGE BODY: APPS.PER_RI_RT_UTIL_PKG
Source
1 PACKAGE BODY per_ri_rt_util_pkg AS
2 /* $Header: perrirtutil.pkb 120.3 2006/10/05 09:54:16 balchand noship $ */
3 PROCEDURE insert_per_ri_rt_gen (p_job_name VARCHAR2,
4 p_request_id NUMBER,
5 p_user_id NUMBER)
6 IS
7 CURSOR csr_get_conc_proc_stat IS
8 SELECT PHASE_CODE,
9 STATUS_CODE,
10 ACTUAL_START_DATE
11 FROM FND_CONCURRENT_REQUESTS fcr
12 WHERE request_id = p_request_id;
13 l_req_phase VARCHAR2(1);
14 l_req_status VARCHAR2(1);
15 l_submit_date DATE;
16 BEGIN
17 OPEN csr_get_conc_proc_stat;
18 FETCH csr_get_conc_proc_stat INTO l_req_phase, l_req_status,l_submit_date;
19 CLOSE csr_get_conc_proc_stat;
20
21 INSERT INTO per_ri_rt_gen (
22 JOB_NAME,
23 REQUEST_ID,
24 USER_ID,
25 REQUEST_PHASE,
26 REQUEST_STATUS,
27 SUBMISSION_DATE,
28 COMPLETION_DATE,
29 TEST_SUITE)
30 VALUES (p_job_name,
31 p_request_id,
32 p_user_id,
33 l_req_phase,
34 l_req_status,
35 sysdate,--l_submit_date,
36 null,
37 null);
38 END insert_per_ri_rt_gen;
39
40 FUNCTION make_sql_stmt (p_value_set_id in number,
41 p_value_set_type in varchar2,
42 p_choice out nocopy boolean)
43 return varchar2 is
44
45 c number;
46 l_stmt varchar2(20000);
47 l_where varchar2(2000) ;
48
49 BEGIN
50
51 IF p_value_set_type = 'F' THEN
52 p_choice := FALSE;
53
54 select 'select to_char('||value_column_name||') display_value, '||
55 replace(id_column_name, ',', ' || ')||' id_value '||
56 ' from '||application_table_name
57 into l_stmt
58 from fnd_flex_validation_tables t
59 where t.flex_value_set_id = p_value_set_id;
60
61 select additional_where_clause
62 into l_where
63 from fnd_flex_validation_tables t
64 where t.flex_value_set_id = p_value_set_id;
65
66 if l_where is not null then
67 l_stmt := l_stmt ||' '||l_where;
68 end if;
69
70 ELSIF p_value_set_type = 'I' THEN
71
72 p_choice := TRUE;
73
74 l_stmt := 'select flex_value_meaning display_value, '||
75 ' flex_value_id id_value '||
76 ' from fnd_flex_values_vl t'||
77 ' where t.flex_value_set_id = :1'|| --//p_value_set_id||
78 ' and t.enabled_flag=''Y''' ;
79 END IF;
80 -- test the SQL
81 c := dbms_sql.open_cursor ;
82 --##MS trap any exceptions raised here so that we can close the cursor before
83 -- raising the error
84 BEGIN
85 dbms_sql.parse(c , l_stmt , dbms_sql.native) ;
86 EXCEPTION
87 WHEN OTHERS THEN
88 dbms_sql.close_cursor(c);
89 RAISE;
90 END;
91
92 --##MS close cursor if the parse raised no exceptions
93 dbms_sql.close_cursor(c);
94
95 return (l_stmt);
96
97 return ( null );
98
99 END make_sql_stmt;
100
101 FUNCTION get_display_prompt (p_flexfield VARCHAR2,
102 p_context VARCHAR2,
103 p_column VARCHAR2) return VARCHAR2
104 IS
105 CURSOR get_window_prompt IS
106 SELECT form_left_prompt
107 FROM fnd_descr_flex_col_usage_vl
108 WHERE descriptive_flexfield_name = p_flexfield
109 AND descriptive_flex_context_code = p_context
110 AND application_column_name = p_column;
111 l_end_user_column_name VARCHAR2(100);
112 BEGIN
113 OPEN get_window_prompt;
114 FETCH get_window_prompt into l_end_user_column_name;
115 CLOSE get_window_prompt;
116 RETURN l_end_user_column_name;
117 END get_display_prompt;
118
119 FUNCTION get_display_value(p_flexfield VARCHAR2,
120 p_context VARCHAR2,
121 p_column VARCHAR2,
122 p_value VARCHAR2) return varchar2
123 IS
124 Cursor get_sel_set IS
125 SELECT nvl(flex_value_set_id,-999)
126 FROM fnd_descr_flex_col_usage_vl
127 WHERE descriptive_flexfield_name = p_flexfield
128 AND descriptive_flex_context_code = p_context
129 AND application_column_name = p_column;
130
131 CURSOR chk_segment_enabled IS
132 SELECT 1
133 FROM fnd_descr_flex_col_usage_vl
134 WHERE descriptive_flexfield_name = p_flexfield
135 AND descriptive_flex_context_code = p_context
136 AND application_column_name = p_column
137 AND display_flag = 'Y'
138 AND ENABLED_FLAG = 'Y';
139
140 CURSOR get_sel_set_type(p_value_set_id NUMBER) IS
141 SELECT validation_type
142 FROM fnd_flex_value_sets
143 WHERE flex_value_set_id = p_value_set_id;
144
145 l_value_set_id NUMBER;
146 l_value_set_type VARCHAR2(1);
147 l_sql_stmt VARCHAR2(20000);
148 l_new_sql_stmt VARCHAR2(20000);
149 c NUMBER;
150 l_value VARCHAR2(200);
151 l_dummy NUMBER;
152 l_choice Boolean;
153 BEGIN
154
155 OPEN get_sel_set;
156 FETCH get_sel_set into l_value_set_id;
157 CLOSE get_sel_set;
158 OPEN chk_segment_enabled;
159 FETCH chk_segment_enabled into l_dummy;
160 if (chk_segment_enabled%NOTFOUND) then
161 CLOSE chk_segment_enabled;
162 return null;
163 else
164 CLOSE chk_segment_enabled;
165 end if;
166
167 if (l_value_set_id = -999 or l_value_set_id is null) then
168 return p_value;
169 end if;
170 if (p_value is null) then return null; end if;
171 hr_utility.trace('Value Set Id is ' || l_value_set_id);
172 IF (l_value_set_id <> -999) THEN
173 OPEN get_sel_set_type(l_value_set_id);
174 FETCH get_sel_set_type INTO l_value_set_type;
175 CLOSE get_sel_set_type;
176 hr_utility.trace('Value set type is ' || l_value_set_type);
177 IF (l_value_set_type = 'F' OR l_value_set_type = 'I') THEN
178 l_sql_stmt := make_sql_stmt(p_value_set_id => l_value_set_id,
179 p_value_set_type =>l_value_set_type,
180 p_choice => l_choice);
181 IF (l_sql_stmt is null ) then
182 hr_utility.trace('Null');
183 RETURN NULL;
184 END IF;
185 hr_utility.trace(l_sql_stmt);
186
187 IF l_choice = TRUE THEN
188
189 l_new_sql_stmt := 'select display_value from (' || l_sql_stmt || ') where id_value = to_char(:p_value)';
190 execute immediate l_new_sql_stmt into l_value using l_value_set_id,p_value;
191
192 ELSIF l_choice = FALSE THEN
193
194 l_new_sql_stmt := 'select display_value from (' || l_sql_stmt || ') where id_value = to_char(:p_value)';
195 execute immediate l_new_sql_stmt into l_value using p_value;
196
197 END IF;
198
199 RETURN l_value;
200 ELSIF (l_value_set_type = 'N') THEN
201 RETURN p_value;
202 ELSE
203 RETURN null;
204 END IF;
205 END IF;
206 EXCEPTION
207 WHEN OTHERS then
208 return null;
209 END get_display_value;
210
211 FUNCTION chk_context (p_flexfield VARCHAR2,
212 p_context VARCHAR2) RETURN NUMBER
213 IS
214 CURSOR chk_segments_for_context IS
215 SELECT count(*)
216 FROM fnd_descr_flex_col_usage_vl
217 WHERE descriptive_flexfield_name = p_flexfield
218 AND descriptive_flex_context_code = p_context;
219 l_number NUMBER;
220 BEGIN
221 OPEN chk_segments_for_context;
222 FETCH chk_segments_for_context INTO l_number;
223 CLOSE chk_segments_for_context;
224 RETURN l_number;
225 END chk_context;
226
227 PROCEDURE generate_xml (p_entity_code VARCHAR2,
228 p_sample_size NUMBER,
229 p_business_group_id NUMBER,
230 p_xmldata OUT nocopy CLOB)
231 IS
232 CURSOR get_legislation_code IS
233 -- SELECT legislation_code
234 -- FROM per_business_groups
235 -- WHERE business_group_id = p_business_group_id;
236
237 select hr_api.return_legislation_code(p_business_group_id)
238 as legislation_code
239 from dual;
240
241 l_query_str VARCHAR2(20000);
242 queryCtx DBMS_XMLQuery.ctxType;
243 xmlString1 CLOB ;
244 l_entityset VARCHAR2(100) ;
245 l_entity VARCHAR2(100) ;
246 l_legislation_code VARCHAR2(10);
247 l_sample_size number;
248 BEGIN
249 if (p_sample_size is null) then
250 l_sample_size := 5;
251 else
252 l_sample_size := p_sample_size;
253 end if;
254 hr_utility.trace('.................1');
255
256 hr_utility.trace('Entering ...........1');
257 IF (p_entity_code = 'ORGANIZATION_HIERARCHY') THEN
258 l_query_str := 'SELECT pos.name OrganizationStructureName,' ||
259 ' pos.primary_structure_flag primaryflag,' ||
260 ' pos.POSITION_CONTROL_STRUCTURE_FLG PosnCtrlStrFlg,' ||
261 ' pov.VERSION_NUMBER Versionnumber,' ||
262 ' to_char(pov.DATE_FROM,''rrrr-mm-dd'') DateFrom,' ||
263 ' to_char(pov.Date_to,''rrrr-mm-dd'') DateTo,' ||
264 ' pov.topnode_pos_ctrl_enabled_flag TopNodeCtrlFlg,' ||
265 ' poe.position_control_enabled_flag PositionCtrlFlg,' ||
266 ' hou1.NAME ParentOrg,' ||
267 ' hou2.NAME ChildOrg' ||
268 ' FROM per_organization_structures_v pos,' ||
269 ' per_org_structure_versions_v pov,' ||
270 ' per_org_structure_elements poe,' ||
271 ' hr_organization_units hou1,' ||
272 ' hr_organization_units hou2' ||
273 ' WHERE poe.org_structure_version_id = pov.org_structure_version_id' ||
274 ' AND pov.organization_structure_id = pos.organization_structure_id' ||
275 ' AND poe.organization_id_parent = hou1.organization_id' ||
276 ' AND poe.organization_id_child = hou2.organization_id' ||
277 ' AND poe.business_group_id = hou1.business_group_id' ||
278 ' AND poe.business_group_id = hou2.business_group_id' ||
279 ' AND poe.business_group_id = :business_group_id'; --|| p_business_group_id;
280 l_entityset := 'OrgHierarchies';
281 l_entity := 'OrgHierarchy';
282 ELSIF (p_entity_code = 'JOB_GROUPS') THEN
283 l_query_str := 'SELECT pjg.displayed_name JobGrpName,' ||
284 ' ffs.id_flex_structure_name JobFlexStr,' ||
285 ' hou.name BGName,' ||
286 ' pjg.MASTER_FLAG MasterFlg' ||
287 ' FROM per_job_groups pjg,' ||
288 ' fnd_id_flex_structures_vl ffs,' ||
289 ' hr_organization_units hou' ||
290 ' WHERE pjg.business_group_id = hou.organization_id' ||
291 ' AND hou.organization_id = hou.business_group_id' ||
292 ' AND pjg.id_flex_num = ffs.id_flex_num' ||
293 ' AND ffs.id_flex_code = ''JOB''' ||
294 ' AND pjg.business_group_id = :business_group_id'; --|| p_business_group_id;
295 l_entityset := 'JobGroups';
296 l_entity := 'JobGroup';
297 ELSIF (p_entity_code = 'JOB_GROUPS_GB') THEN
298 l_query_str := 'SELECT pjg.displayed_name JobGrpName,' ||
299 ' ffs.id_flex_structure_name JobFlexStr,' ||
300 ' hou.name BGName,' ||
301 ' pjg.MASTER_FLAG MasterFlg' ||
302 ' FROM per_job_groups pjg,' ||
303 ' fnd_id_flex_structures_vl ffs,' ||
304 ' hr_organization_units hou' ||
305 ' WHERE pjg.business_group_id = hou.organization_id' ||
306 ' AND hou.organization_id = hou.business_group_id' ||
307 ' AND pjg.id_flex_num = ffs.id_flex_num' ||
308 ' AND ffs.id_flex_code = ''JOB''' ||
309 ' AND pjg.business_group_id = :business_group_id'; --|| p_business_group_id;
310 l_entityset := 'JobGroups';
311 l_entity := 'JobGroup';
312 ELSIF (p_entity_code = 'LOCATIONS') THEN
313 l_query_str := 'SELECT hl.location_code LocationCode, ' ||
314 ' hl.description Description, ' ||
315 ' to_char(hl.inactive_date,''rrrr-mm-dd'') InactiveDate, ' ||
316 ' ffc.descriptive_flex_context_name Style, ' ||
317 ' hl.ship_to_site_flag ShiptoSiteFlag, ' ||
318 ' hl.receiving_site_flag ReceivingSiteFlag,' ||
319 ' hl.bill_to_site_flag BillToSiteFlag, ' ||
320 ' hl.in_organization_flag InOrgFlag, ' ||
321 ' hl.office_site_flag OfficeSiteFlag, ' ||
322 ' hl1.location_code ShipToLocation, ' ||
323 ' pap.full_name Contact, ' ||
324 ' hou.NAME InventoryOrg, ' ||
325 ' hod2.organization_name TaxCode, ' ||
326 ' hl.ECE_TP_LOCATION_CODE EDILocnCode,' ||
327 ' decode(per_ri_rt_util_pkg.chk_context(''Address Location'',hl.STYLE),0,null,''LOC_ADDRESS_0'') flexfield,' ||
328 ' decode(per_ri_rt_util_pkg.chk_context(''Address Location'',hl.STYLE),0,null,''Location Address'') curform,' ||
329 ' decode(per_ri_rt_util_pkg.chk_context(''Address Location'',hl.STYLE),0,null,''Cancel'') button,' ||
330 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''ADDRESS_LINE_1'') Addressline1pmt, ' ||
331 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''ADDRESS_LINE_2'') Addressline2pmt, ' ||
332 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''ADDRESS_LINE_3'') Addressline3pmt, ' ||
333 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''COUNTRY'') countrypmt, ' ||
334 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''LOC_INFORMATION13'') LocInformation13pmt, ' ||
335 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''LOC_INFORMATION14'') LocInformation14pmt, ' ||
336 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''LOC_INFORMATION15'') LocInformation15pmt, ' ||
337 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''LOC_INFORMATION16'') LocInformation16pmt, ' ||
338 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''LOC_INFORMATION17'') LocInformation17pmt, ' ||
339 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''LOC_INFORMATION18'') LocInformation18pmt, ' ||
340 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''LOC_INFORMATION19'') LocInformation19pmt, ' ||
341 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''LOC_INFORMATION20'') LocInformation20pmt, ' ||
342 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''POSTAL_CODE'') PostalCodepmt, ' ||
343 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''REGION_1'') Region1pmt, ' ||
344 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''REGION_2'') Region2pmt, ' ||
345 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''region_3'') Region3pmt, ' ||
346 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''TELEPHONE_NUMBER_1'') TelephoneNumber1pmt, ' ||
347 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''TELEPHONE_NUMBER_2'') TelephoneNumber2pmt, ' ||
348 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''TELEPHONE_NUMBER_3'') TelephoneNumber3pmt, ' ||
349 ' per_ri_rt_util_pkg.get_display_prompt ( ''Address Location'',hl.STYLE,''TOWN_OR_CITY'') TownOrCitypmt, ' ||
350 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''ADDRESS_LINE_1'',hl.address_line_1) Addressline1, ' ||
351 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''ADDRESS_LINE_2'',hl.address_line_2) Addressline2, ' ||
352 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''ADDRESS_LINE_3'',hl.address_line_3) Addressline3, ' ||
353 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''COUNTRY'',hl.COUNTRY) country, ' ||
354 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''LOC_INFORMATION13'',hl.loc_information13) LocInformation13, ' ||
355 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''LOC_INFORMATION14'',hl.loc_information14) LocInformation14, ' ||
356 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''LOC_INFORMATION15'',hl.loc_information15) LocInformation15, ' ||
357 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''LOC_INFORMATION16'',hl.loc_information16) LocInformation16, ' ||
358 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''LOC_INFORMATION17'',hl.loc_information17) LocInformation17, ' ||
359 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''LOC_INFORMATION18'',hl.loc_information18) LocInformation18, ' ||
360 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''LOC_INFORMATION19'',hl.loc_information19) LocInformation19, ' ||
361 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''LOC_INFORMATION20'',hl.loc_information20) LocInformation20, ' ||
362 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''POSTAL_CODE'',hl.POSTAL_CODE) PostalCode, ' ||
363 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''REGION_1'',hl.REGION_1) Region1, ' ||
364 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''REGION_2'',hl.REGION_2) Region2, ' ||
365 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''region_3'',hl.region_3) Region3, ' ||
366 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''TELEPHONE_NUMBER_1'',hl.TELEPHONE_NUMBER_1) TelephoneNumber1, ' ||
367 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''TELEPHONE_NUMBER_2'',hl.telephone_number_2) TelephoneNumber2, ' ||
368 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''TELEPHONE_NUMBER_3'',hl.telephone_number_3) TelephoneNumber3, ' ||
369 ' per_ri_rt_util_pkg.get_display_value ( ''Address Location'',hl.STYLE,''TOWN_OR_CITY'',hl.town_or_city) TownOrCity ' ||
370 ' FROM hr_locations hl, ' ||
371 ' fnd_descr_flex_contexts_vl ffc, ' ||
372 ' hr_locations hl1, ' ||
373 ' per_all_people pap, ' ||
374 ' hr_organization_units hou, ' ||
375 ' org_organization_definitions hod2 ' ||
376 ' WHERE ffc.descriptive_flex_context_code = hl.STYLE ' ||
377 ' AND ffc.descriptive_flexfield_name = ''Address Location'' ' ||
378 ' AND hl.ship_to_location_id = hl1.LOCATION_ID ' ||
379 ' AND hl.designated_receiver_id = pap.person_id(+) ' ||
380 ' AND hl.INVENTORY_ORGANIZATION_ID = hou.organization_id(+) ' ||
381 ' AND hl.tax_name = hod2.organization_code(+)' ||
382 ' AND (hl.business_group_id is null or hl.business_group_id = :business_group_id)'; --' || p_business_group_id || ')';
383 l_entityset := 'Locations';
384 l_entity := 'Location';
385 ELSIF (p_entity_code = 'JOBS') THEN
386 l_query_str := 'SELECT pjg.displayed_name JobGroup,' ||
387 ' pjv.name jobname,' ||
388 ' to_char(pjv.date_from,''rrrr-mm-dd'') datefrom,' ||
389 ' to_char(pjv.date_to,''rrrr-mm-dd'') dateto,' ||
390 ' pjv.approval_authority approvalauthority,' ||
391 ' pjv.emp_rights_flag emprightsflag,' ||
392 ' pjv.benchmark_job_flag benchmarkFlag,' ||
393 ' pj1.name benchmarkjobname,' ||
394 ' decode(per_ri_rt_util_pkg.chk_context(''Job Developer DF'',pjv.job_information_category),0,null,''JOBS_DEVELOPER_DF_0'') flexfield,' ||
395 ' decode(per_ri_rt_util_pkg.chk_context(''Job Developer DF'',pjv.job_information_category),0,null,''Further Job Information'') curform,' ||
396 ' decode(per_ri_rt_util_pkg.chk_context(''Job Developer DF'',pjv.job_information_category),0,null,''Cancel'') button,' ||
397 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION1'') JobInformationprompt1,' ||
398 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION2'') JobInformationprompt2,' ||
399 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION3'') JobInformationprompt3,' ||
400 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION4'') JobInformationprompt4,' ||
401 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION5'') JobInformationprompt5,' ||
402 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION6'') JobInformationprompt6,' ||
403 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION7'') JobInformationprompt7,' ||
404 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION8'') JobInformationprompt8,' ||
405 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION9'') JobInformationprompt9,' ||
406 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION10'') JobInformationprompt10,' ||
407 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION11'') JobInformationprompt11,' ||
408 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION12'') JobInformationprompt12,' ||
409 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION13'') JobInformationprompt13,' ||
410 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION14'') JobInformationprompt14,' ||
411 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION15'') JobInformationprompt15,' ||
412 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION16'') JobInformationprompt16,' ||
413 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION17'') JobInformationprompt17,' ||
414 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION18'') JobInformationprompt18,' ||
415 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION19'') JobInformationprompt19,' ||
416 ' per_ri_rt_util_pkg.get_display_prompt (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION20'') JobInformationprompt20,' ||
417 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION1'',pjv.job_information1) JobInformation1,' ||
418 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION2'',pjv.job_information2) JobInformation2,' ||
419 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION3'',pjv.job_information3) JobInformation3,' ||
420 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION4'',pjv.job_information4) JobInformation4,' ||
421 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION5'',pjv.job_information5) JobInformation5,' ||
422 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION6'',pjv.job_information6) JobInformation6,' ||
423 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION7'',pjv.job_information7) JobInformation7,' ||
424 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION8'',pjv.job_information8) JobInformation8,' ||
425 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION9'',pjv.job_information9) JobInformation9,' ||
426 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION10'',pjv.job_information10) JobInformation10,' ||
427 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION11'',pjv.job_information11) JobInformation11,' ||
428 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION12'',pjv.job_information12) JobInformation12,' ||
429 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION13'',pjv.job_information13) JobInformation13,' ||
430 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION14'',pjv.JOB_INFORMATION14) JobInformation14,' ||
431 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION15'',pjv.job_information15) JobInformation15,' ||
432 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION16'',pjv.job_information16) JobInformation16,' ||
433 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION17'',pjv.job_information17) JobInformation17,' ||
434 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION18'',pjv.job_information18) JobInformation18,' ||
435 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION19'',pjv.job_information19) JobInformation19,' ||
436 ' per_ri_rt_util_pkg.get_display_value (''Job Developer DF'',pjv.job_information_category,''JOB_INFORMATION20'',pjv.job_information20) JobInformation20' ||
437 ' FROM per_job_groups pjg,' ||
438 ' per_jobs_vl pjv,' ||
439 ' per_jobs pj1' ||
440 ' WHERE pjg.JOB_GROUP_ID = pjv.JOB_GROUP_ID ' ||
441 ' AND pjv.BENCHMARK_JOB_ID = pj1.JOB_ID(+)' ||
442 ' AND pjv.business_group_id = :business_group_id'; --|| p_business_group_id;
443
444 l_entityset := 'Jobs';
445 l_entity := 'Job';
446 ELSIF (p_entity_code = 'GRADES') THEN
447 l_query_str := 'select pgv.SEQUENCE SequenceNo,' ||
448 ' pgv.NAME Name,' ||
449 ' pgv.SHORT_NAME ShortName,' ||
450 ' to_char(pgv.date_from,''rrrr-mm-dd'') DateFrom,' ||
451 ' to_char(pgv.date_to,''rrrr-mm-dd'') DateTo,' ||
452 ' decode(per_ri_rt_util_pkg.chk_context(''PER_GRADES'',pgv.attribute_category),0,null,''GRD1_DF_0'') flexfield,' ||
453 ' decode(per_ri_rt_util_pkg.chk_context(''PER_GRADES'',pgv.attribute_category),0,null,''Additional Grade Details'') curform,' ||
454 ' decode(per_ri_rt_util_pkg.chk_context(''PER_GRADES'',pgv.attribute_category),0,null,''Cancel'') Button,' ||
455 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE1'') Attributeprompt1,' ||
456 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE2'') Attributeprompt2,' ||
457 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE3'') Attributeprompt3,' ||
458 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE4'') Attributeprompt4,' ||
459 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE5'') Attributeprompt5,' ||
460 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE6'') Attributeprompt6,' ||
461 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE7'') Attributeprompt7,' ||
462 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE8'') Attributeprompt8,' ||
463 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE9'') Attributeprompt9,' ||
464 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE10'') Attributeprompt10,' ||
465 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE11'') Attributeprompt11,' ||
466 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE12'') Attributeprompt12,' ||
467 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE13'') Attributeprompt13,' ||
468 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE14'') Attributeprompt14,' ||
469 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE15'') Attributeprompt15,' ||
470 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE16'') Attributeprompt16,' ||
471 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE17'') Attributeprompt17,' ||
472 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE18'') Attributeprompt18,' ||
473 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE19'') Attributeprompt19,' ||
474 ' per_ri_rt_util_pkg.get_display_prompt (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE20'') Attributeprompt20,' ||
475 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE1'',pgv.attribute1) Attribute1,' ||
476 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE2'',pgv.attribute2) Attribute2,' ||
477 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE3'',pgv.attribute3) Attribute3,' ||
478 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE4'',pgv.attribute4) Attribute4,' ||
479 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE5'',pgv.attribute5) Attribute5,' ||
480 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE6'',pgv.attribute6) Attribute6,' ||
481 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE7'',pgv.attribute7) Attribute7,' ||
482 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE8'',pgv.attribute8) Attribute8,' ||
483 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE9'',pgv.attribute9) Attribute9,' ||
484 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE10'',pgv.attribute10) Attribute10,' ||
485 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE11'',pgv.attribute11) Attribute11,' ||
486 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE12'',pgv.attribute12) Attribute12,' ||
487 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE13'',pgv.attribute13) Attribute13,' ||
488 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE14'',pgv.attribute14) Attribute14,' ||
489 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE15'',pgv.attribute15) Attribute15,' ||
490 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE16'',pgv.attribute16) Attribute16,' ||
491 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE17'',pgv.attribute17) Attribute17,' ||
492 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE18'',pgv.attribute18) Attribute18,' ||
493 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE19'',pgv.attribute19) Attribute19,' ||
494 ' per_ri_rt_util_pkg.get_display_value (''PER_GRADES'',pgv.attribute_category,''ATTRIBUTE20'',pgv.attribute20) Attribute20 ' ||
495 ' from per_grades_vl pgv '||
496 ' where pgv.business_group_id = :business_group_id'; --|| p_business_group_id;
497
498 l_entityset := 'Grades';
499 l_entity := 'Grade';
500 ELSIF (p_entity_code = 'PAYROLLS') THEN
501 l_query_str := 'select ppv.payroll_name PayrollName, ' ||
502 ' ppv.display_period_type PeriodType, ' ||
503 ' to_char(ppv.first_period_end_date,''rrrr-mm-dd'') FirstPeriodEndDate, ' ||
504 ' ppv.number_of_years NumberofYears, ' ||
505 ' ppv.pay_date_offset PayDateOffset, ' ||
506 ' ppv.direct_deposit_date_offset DirectDepDateOffset, ' ||
507 ' ppv.cut_off_date_offset CutOffDateOffset, ' ||
508 ' ppv.payment_method PaymentMethod, ' ||
509 ' ppv.consolidation_set ConsolidationSet, ' ||
510 ' to_char(ppv.effective_start_date,''rrrr-mm-dd'') StartDate, ' ||
511 ' to_char(ppv.effective_end_date,''rrrr-mm-dd'') EndDate, ' ||
512 ' ppv.negative_pay_allowed_flag NegPayAllowed, ' ||
513 ' ppv.multi_assignments_flag MultiAssgFlag, ' ||
514 ' decode(per_ri_rt_util_pkg.chk_context(''Payroll Developer DF'',ppv.prl_information_category),0,null,''PRL_PAYROLL_DESC_FLEX_0'') flexfield,' ||
515 ' decode(per_ri_rt_util_pkg.chk_context(''Payroll Developer DF'',ppv.prl_information_category),0,null,''Further Payroll Information'') curform,' ||
516 ' decode(per_ri_rt_util_pkg.chk_context(''Payroll Developer DF'',ppv.prl_information_category),0,null,''Cancel'') Button,' ||
517 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION1'') PayrollInfoprompt1, ' ||
518 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION2'') PayrollInfoprompt2, ' ||
519 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION3'') PayrollInfoprompt3, ' ||
520 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION4'') PayrollInfoprompt4, ' ||
521 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION5'') PayrollInfoprompt5, ' ||
522 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION6'') PayrollInfoprompt6, ' ||
523 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION7'') PayrollInfoprompt7, ' ||
524 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION8'') PayrollInfoprompt8, ' ||
525 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION9'') PayrollInfoprompt9, ' ||
526 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION10'') PayrollInfoprompt10, ' ||
527 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION11'') PayrollInfoprompt11, ' ||
528 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION12'') PayrollInfoprompt12, ' ||
529 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION13'') PayrollInfoprompt13, ' ||
530 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION14'') PayrollInfoprompt14, ' ||
531 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION15'') PayrollInfoprompt15, ' ||
532 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION16'') PayrollInfoprompt16, ' ||
533 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION17'') PayrollInfoprompt17, ' ||
534 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION18'') PayrollInfoprompt18, ' ||
535 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION19'') PayrollInfoprompt19, ' ||
536 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION20'') PayrollInfoprompt20, ' ||
537 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION21'') PayrollInfoprompt21, ' ||
538 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION22'') PayrollInfoprompt22, ' ||
539 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION23'') PayrollInfoprompt23, ' ||
540 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION24'') PayrollInfoprompt24, ' ||
541 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION25'') PayrollInfoprompt25, ' ||
542 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION26'') PayrollInfoprompt26, ' ||
543 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION27'') PayrollInfoprompt27, ' ||
544 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION28'') PayrollInfoprompt28, ' ||
545 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION29'') PayrollInfoprompt29, ' ||
546 ' per_ri_rt_util_pkg.get_display_prompt (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION30'') PayrollInfoprompt30, ' ||
547 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION1'',ppv.prl_information1) PayrollInformaion1, ' ||
548 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION2'',ppv.prl_information2) PayrollInformaion2, ' ||
549 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION3'',ppv.prl_information3) PayrollInformaion3, ' ||
550 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION4'',ppv.prl_information4) PayrollInformaion4, ' ||
551 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION5'',ppv.prl_information5) PayrollInformaion5, ' ||
552 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION6'',ppv.prl_information6) PayrollInformaion6, ' ||
553 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION7'',ppv.prl_information7) PayrollInformaion7, ' ||
554 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION8'',ppv.prl_information8) PayrollInformaion8, ' ||
555 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION9'',ppv.prl_information9) PayrollInformaion9, ' ||
556 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION10'',ppv.prl_information10) PayrollInformaion10, ' ||
557 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION11'',ppv.prl_information11) PayrollInformaion11, ' ||
558 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION12'',ppv.prl_information12) PayrollInformaion12, ' ||
559 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION13'',ppv.prl_information13) PayrollInformaion13, ' ||
560 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION14'',ppv.prl_information14) PayrollInformaion14, ' ||
561 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION15'',ppv.prl_information15) PayrollInformaion15, ' ||
562 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION16'',ppv.prl_information16) PayrollInformaion16, ' ||
563 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION17'',ppv.prl_information17) PayrollInformaion17, ' ||
564 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION18'',ppv.prl_information18) PayrollInformaion18, ' ||
565 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION19'',ppv.prl_information19) PayrollInformaion19, ' ||
566 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION20'',ppv.prl_information20) PayrollInformaion20, ' ||
567 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION21'',ppv.prl_information21) PayrollInformaion21, ' ||
568 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION22'',ppv.prl_information22) PayrollInformaion22, ' ||
569 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION23'',ppv.prl_information23) PayrollInformaion23, ' ||
570 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION24'',ppv.prl_information24) PayrollInformaion24, ' ||
571 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION25'',ppv.prl_information25) PayrollInformaion25, ' ||
572 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION26'',ppv.prl_information26) PayrollInformaion26, ' ||
573 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION27'',ppv.prl_information27) PayrollInformaion27, ' ||
574 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION28'',ppv.prl_information28) PayrollInformaion28, ' ||
575 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION29'',ppv.prl_information29) PayrollInformaion29, ' ||
576 ' per_ri_rt_util_pkg.get_display_value (''Payroll Developer DF'',ppv.prl_information_category,''PRL_INFORMATION30'',ppv.prl_information30) PayrollInformaion30 ' ||
577 ' from pay_payrolls_v2 ppv' ||
578 ' where ppv.business_group_id = :business_group_id'; --|| p_business_group_id;
579
580 l_entityset := 'Payrolls';
581 l_entity := 'Payroll';
582 ELSIF (p_entity_code = 'ELEMENTS') THEN
583 hr_utility.trace('Elements.........1');
584 l_query_str := 'SELECT pet.element_name ElementName,' ||
585 ' pet.reporting_name ReportingName,' ||
586 ' pet.description Description,' ||
587 ' pet.classification_name Classification,' ||
588 ' pet.benefit_classification_name BenefitClassification,' ||
589 ' to_char(pet.effective_start_date,''rrrr-mm-dd'') StartDate,' ||
590 ' decode(to_char(pet.effective_end_date,''rrrr-mm-dd''),''4712-12-31'',null,to_char(pet.effective_end_date,''rrrr-mm-dd'')) EndDate,' ||
591 ' pet.multiply_value_flag RecurringFlag,' ||
592 ' pet.post_termination_rule PostTerminationRule,' ||
593 ' pet.processing_priority Priority,' ||
594 ' pet.formula_name SkipRule,' ||
595 ' pet.multiple_entries_allowed_flag MultipleEntriesAllowed,' ||
596 ' pet.additional_entry_allowed_flag AddEntryAllowed,' ||
597 ' pet.closed_for_entry_flag ClosedForEntry,' ||
598 ' pet.process_in_run_flag ProcessIfRun,' ||
599 ' pet.indirect_only_flag IndirectOnlyFlag,' ||
600 ' pet.adjustment_only_flag AdjOnlyFlg,' ||
601 ' pet.third_party_pay_only_flag ThirdPartyPayFlg,' ||
602 ' pet.input_currency_code IPCurrencyCode,' ||
603 ' pet.output_currency_code OPCurrencyCode,' ||
604 ' pet.qualifying_age Age,' ||
605 ' pet.qualifying_length_of_service LegnthOfSrvc,' ||
606 ' pet.qualifying_unit_name Units,' ||
607 ' pet.standard_link_flag Standard,' ||
608 ' pet.grossup_flag GrossUpFlag,' ||
609 ' pet.iterative_flag IterativeFlag,' ||
610 ' pet.iterative_formula_name IterativeFormula,' ||
611 ' pet.iterative_priority IterativePriority,' ||
612 ' pet.separate_payment SeperatePayment,' ||
613 ' pet.process_separate_flag SeperateFlg,' ||
614 ' pet.d_retro_summ_ele_name RetroSummEleName,' ||
615 ' pet.proration_group_name ProrationGroup,' ||
616 ' pet.proration_formula_name ProrationFormula,' ||
617 ' decode(per_ri_rt_util_pkg.chk_context(''Element Developer DF'',pet.element_information_category),0,null,''ELEMENT_TYPES_DEVELOPER_DF_0'') flexfield,' ||
618 ' decode(per_ri_rt_util_pkg.chk_context(''Element Developer DF'',pet.element_information_category),0,null,''Further Element Information'') curform,' ||
619 ' decode(per_ri_rt_util_pkg.chk_context(''Element Developer DF'',pet.element_information_category),0,null,''Cancel'') Button,' ||
620 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION1'') ElementInfoprompt1,' ||
621 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION2'') ElementInfoprompt2,' ||
622 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION3'') ElementInfoprompt3,' ||
623 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION4'') ElementInfoprompt4,' ||
624 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION5'') ElementInfoprompt5,' ||
625 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION6'') ElementInfoprompt6,' ||
626 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION7'') ElementInfoprompt7,' ||
627 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION8'') ElementInfoprompt8,' ||
628 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION9'') ElementInfoprompt9,' ||
629 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION10'') ElementInfoprompt10,' ||
630 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION11'') ElementInfoprompt11,' ||
631 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION12'') ElementInfoprompt12,' ||
632 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION13'') ElementInfoprompt13,' ||
633 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION14'') ElementInfoprompt14,' ||
634 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION15'') ElementInfoprompt15,' ||
635 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION16'') ElementInfoprompt16,' ||
636 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION17'') ElementInfoprompt17,' ||
637 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION18'') ElementInfoprompt18,' ||
638 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION19'') ElementInfoprompt19,' ||
639 ' per_ri_rt_util_pkg.get_display_prompt (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION20'') ElementInfoprompt20,' ||
640 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION1'',pet.element_information1) ElementInfo1,' ||
641 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION2'',pet.element_information2) ElementInfo2,' ||
642 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION3'',pet.element_information3) ElementInfo3,' ||
643 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION4'',pet.element_information4) ElementInfo4,' ||
644 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION5'',pet.element_information5) ElementInfo5,' ||
645 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION6'',pet.element_information6) ElementInfo6,' ||
646 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION7'',pet.element_information7) ElementInfo7,' ||
647 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION8'',pet.element_information8) ElementInfo8,' ||
648 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION9'',pet.element_information9) ElementInfo9,' ||
649 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION10'',pet.element_information10) ElementInfo10,' ||
650 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION11'',pet.element_information11) ElementInfo11,' ||
651 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION12'',pet.element_information12) ElementInfo12,' ||
652 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION13'',pet.element_information13) ElementInfo13,' ||
653 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION14'',pet.element_information14) ElementInfo14,' ||
654 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION15'',pet.element_information15) ElementInfo15,' ||
655 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION16'',pet.element_information16) ElementInfo16,' ||
656 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION17'',pet.element_information17) ElementInfo17,' ||
657 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION18'',pet.element_information18) ElementInfo18,' ||
658 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION19'',pet.element_information19) ElementInfo19,' ||
659 ' per_ri_rt_util_pkg.get_display_value (''Element Developer DF'',pet.element_information_category,''ELEMENT_INFORMATION20'',pet.element_information20) ElementInfo20 ' ||
660 ' FROM pay_element_types_denorm_v pet ' ||
661 ' WHERE pet.business_group_id = :business_group_id'; --|| p_business_group_id;
662
663 l_entityset := 'Elements';
664 l_entity := 'Element';
665 ELSIF (p_entity_code = 'SALARY_BASES') THEN
666 hr_utility.trace('Salary Bases.........1');
667 l_query_str := 'select ppb.name Name, ' ||
668 ' hl.meaning PayBasis, ' ||
669 ' ppb.pay_annualization_factor AnnualFactor, ' ||
670 ' pet.element_name Element, ' ||
671 ' pit.name InputValue, ' ||
672 ' pr.name GradeRate, ' ||
673 ' ppb.grade_annualization_factor GradeAnnualFactor, ' ||
674 ' to_char(piv.effective_start_date,''rrrr-mm-dd'') StartDate, ' ||
675 ' decode(to_char(piv.effective_end_date,''rrrr-mm-dd''),''4712-12-31'',null,to_char(piv.effective_end_date,''rrrr-mm-dd'')) EndDate, ' ||
676 ' decode(per_ri_rt_util_pkg.chk_context(''PER_PAY_BASES'',ppb.attribute_category),0,null,''PAYBAS_DF_ITEM_0'') flexfield,' ||
677 ' decode(per_ri_rt_util_pkg.chk_context(''PER_PAY_BASES'',ppb.attribute_category),0,null,''Additional Salary Basis Details'') curform,' ||
678 ' decode(per_ri_rt_util_pkg.chk_context(''PER_PAY_BASES'',ppb.attribute_category),0,null,''Cancel'') Button,' ||
679 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE1'') Attributeprompt1, ' ||
680 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE2'') Attributeprompt2, ' ||
681 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE3'') Attributeprompt3, ' ||
682 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE4'') Attributeprompt4, ' ||
683 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE5'') Attributeprompt5, ' ||
684 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE6'') Attributeprompt6, ' ||
685 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE7'') Attributeprompt7, ' ||
686 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE8'') Attributeprompt8, ' ||
687 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE9'') Attributeprompt9, ' ||
688 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE10'') Attributeprompt10, ' ||
689 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE11'') Attributeprompt11, ' ||
690 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE12'') Attributeprompt12, ' ||
691 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE13'') Attributeprompt13, ' ||
692 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE14'') Attributeprompt14, ' ||
693 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE15'') Attributeprompt15, ' ||
694 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE16'') Attributeprompt16, ' ||
695 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE17'') Attributeprompt17, ' ||
696 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE18'') Attributeprompt18, ' ||
697 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE19'') Attributeprompt19, ' ||
698 ' per_ri_rt_util_pkg.get_display_prompt (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE20'') Attributeprompt20, ' ||
699 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE1'',ppb.attribute1) Attribute1, ' ||
700 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE2'',ppb.attribute2) Attribute2, ' ||
701 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE3'',ppb.attribute3) Attribute3, ' ||
702 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE4'',ppb.attribute4) Attribute4, ' ||
703 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE5'',ppb.attribute5) Attribute5, ' ||
704 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE6'',ppb.attribute6) Attribute6, ' ||
705 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE7'',ppb.attribute7) Attribute7, ' ||
706 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE8'',ppb.attribute8) Attribute8, ' ||
707 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE9'',ppb.attribute9) Attribute9, ' ||
708 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE10'',ppb.attribute10) Attribute10, ' ||
709 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE11'',ppb.attribute11) Attribute11, ' ||
710 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE12'',ppb.attribute12) Attribute12, ' ||
711 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE13'',ppb.attribute13) Attribute13, ' ||
712 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE14'',ppb.attribute14) Attribute14, ' ||
713 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE15'',ppb.attribute15) Attribute15, ' ||
714 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE16'',ppb.attribute16) Attribute16, ' ||
715 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE17'',ppb.attribute17) Attribute17, ' ||
716 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE18'',ppb.attribute18) Attribute18, ' ||
717 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE19'',ppb.attribute19) Attribute19, ' ||
718 ' per_ri_rt_util_pkg.get_display_value (''PER_PAY_BASES'',ppb.attribute_category,''ATTRIBUTE20'',ppb.attribute20) Attribute20 ' ||
719 ' from per_pay_bases ppb, ' ||
720 ' hr_lookups hl, ' ||
721 ' pay_input_values_f piv, ' ||
722 ' pay_input_values_f_tl pit, ' ||
723 ' pay_element_types pet, ' ||
724 ' pay_rates pr ' ||
725 ' where ppb.PAY_BASIS = hl.lookup_code ' ||
726 ' and hl.lookup_type = ''PAY_BASIS'' ' ||
727 ' and ppb.INPUT_VALUE_ID = piv.INPUT_VALUE_ID ' ||
728 ' and piv.input_value_id = pit.INPUT_VALUE_ID ' ||
729 ' and piv.element_type_id = pet.element_type_id ' ||
730 ' and ppb.RATE_ID = pr.RATE_ID(+) ' ||
731 ' and pit.language = ''US'' ' ||
732 ' and ppb.business_group_id = :business_group_id'; --|| p_business_group_id;
733
734 l_entityset := 'SalaryBases';
735 l_entity := 'SalaryBasis';
736 ELSIF (p_entity_code = 'POSITIONS') THEN
737 l_query_str := 'select hpv.NAME Name, ' ||
738 ' hpv.review_flag ReviewFlag, ' ||
739 ' to_char(hpv.date_effective,''rrrr-mm-dd'') StartDate, ' ||
740 ' hpv.date_effective_name DateEffName, ' ||
741 ' hpv.position_type_desc PositionType, ' ||
742 ' hpv.permanent_temporary_flag PermanentFlg, ' ||
743 ' hpv.seasonal_flag SeasonalFlg, ' ||
744 ' hpv.organization_desc OrganizationName, ' ||
745 ' hpv.job_desc Job, ' ||
746 ' to_char(hpv.current_job_prop_end_date,''rrrr-mm-dd'') JobPropEndDate, ' ||
747 ' to_char(hpv.current_org_prop_end_date,''rrrr-mm-dd'') OrgPropEndDate, ' ||
748 ' hpv.availability_status_desc AvailStatus, ' ||
749 ' to_char(hpv.availability_status_start_date,''rrrr-mm-dd'') AvailStartDate, ' ||
750 ' to_char(hpv.avail_status_prop_end_date,''rrrr-mm-dd'') AvailEndDate, ' ||
751 ' hpv.location_desc Location, ' ||
752 ' hpv.status_desc Status, ' ||
753 ' to_char(hpv.effective_start_date,''rrrr-mm-dd'') EffStartDate, ' ||
754 ' to_char(hpv.effective_end_date,''rrrr-mm-dd'') EffEndDate, ' ||
755 ' hpv.fte FTE, ' ||
756 ' hpv.max_persons HeadCount, ' ||
757 ' hpv.bargaining_unit_desc BargainingUnit, ' ||
758 ' to_char(hpv.earliest_hire_date,''rrrr-mm-dd'') EarliestHireDate, ' ||
759 ' to_char(hpv.fill_by_date,''rrrr-mm-dd'') FillByDate, ' ||
760 ' hpv.permit_recruitment_flag PermitRecruitingFlg, ' ||
761 ' hpv.pay_freq_payroll_desc Payroll, ' ||
762 ' hpv.pay_basis_desc SalaryBasis, ' ||
763 ' hpv.entry_grade_desc Grade, ' ||
764 ' hpv.entry_step_desc GradeStep, ' ||
765 ' hpv.grade_rate_value GradeScaleRate, ' ||
766 ' hpv.point_value Value, ' ||
767 ' hpv.grade_rate_min GradeRateRangeMin, ' ||
768 ' hpv.grade_rate_mid GradeRateRangeMid, ' ||
769 ' hpv.grade_rate_max GradeRateRangeMax, ' ||
770 ' hpv.probation_period ProbationDuration, ' ||
771 ' hpv.probation_unit_desc ProbDurationUnit, ' ||
772 ' hpv.overlap_period OverlapDuration, ' ||
773 ' hpv.overlap_unit_desc OverlapDurationUnit, ' ||
774 ' hpv.proposed_fte_for_layoff ProposedLayoffFTE, ' ||
775 ' to_char(hpv.proposed_date_for_layoff,''rrrr-mm-dd'') ProposedLayoffDate, ' ||
776 ' hpv.working_hours WorkingHrs, ' ||
777 ' hpv.frequency_desc Frequency, ' ||
778 ' hpv.time_normal_start NormalTimeStart, ' ||
779 ' hpv.time_normal_finish NormalTimeFinish, ' ||
780 ' hpv.supervisor_desc Supervisor, ' ||
781 ' hpv.replacement_required_flag ReplacementReqFlg, ' ||
782 ' hpv.works_council_approval_flag WorkCouncilApprFlg, ' ||
783 ' hpv.supervisor_position_desc SupervisorPosn, ' ||
784 ' hpv.relief_position_desc Relief, ' ||
785 ' hpv.successor_position_desc Successor, ' ||
786 ' hpv.work_period_type_cd ExtendedPayFlg, ' ||
787 ' hpv.term_start_day_desc WorkTermStartDay, ' ||
788 ' hpv.term_start_month_desc WorkTermStartMonth, ' ||
789 ' hpv.work_term_end_day_desc WorkTermEndDay, ' ||
790 ' hpv.work_term_end_month_desc WorkTermEndMonth, ' ||
791 ' hpv.pay_term_end_day_desc PayTermEndDay, ' ||
792 ' hpv.pay_term_end_month_desc PayTermEndMonth, ' ||
793 ' decode(per_ri_rt_util_pkg.chk_context(''Position Developer DF'',hpv.information_category),0,null,''POSITIONS_POS_DEVELOPER_DF_0'') flexfield,' ||
794 ' decode(per_ri_rt_util_pkg.chk_context(''Position Developer DF'',hpv.information_category),0,null,''Further Position Information'') curform,' ||
795 ' decode(per_ri_rt_util_pkg.chk_context(''Position Developer DF'',hpv.information_category),0,null,''Cancel'') Button,' ||
796 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION1'') Infoprompt1, ' ||
797 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION2'') Infoprompt2, ' ||
798 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION3'') Infoprompt3, ' ||
799 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION4'') Infoprompt4, ' ||
800 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION5'') Infoprompt5, ' ||
801 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION6'') Infoprompt6, ' ||
802 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION7'') Infoprompt7, ' ||
803 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION8'') Infoprompt8, ' ||
804 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION9'') Infoprompt9, ' ||
805 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION10'') Infoprompt10, ' ||
806 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION11'') Infoprompt11, ' ||
807 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION12'') Infoprompt12, ' ||
808 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION13'') Infoprompt13, ' ||
809 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION14'') Infoprompt14, ' ||
810 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION15'') Infoprompt15, ' ||
811 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION16'') Infoprompt16, ' ||
812 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION17'') Infoprompt17, ' ||
813 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION18'') Infoprompt18, ' ||
814 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION19'') Infoprompt19, ' ||
815 ' per_ri_rt_util_pkg.get_display_prompt (''Position Developer DF'',hpv.information_category,''INFORMATION20'') Infoprompt20, ' ||
816 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION1'',hpv.information1) Information1, ' ||
817 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION2'',hpv.information2) Information2, ' ||
818 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION3'',hpv.information3) Information3, ' ||
819 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION4'',hpv.information4) Information4, ' ||
820 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION5'',hpv.information5) Information5, ' ||
821 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION6'',hpv.information6) Information6, ' ||
822 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION7'',hpv.information7) Information7, ' ||
823 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION8'',hpv.information8) Information8, ' ||
824 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION9'',hpv.information9) Information9, ' ||
825 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION10'',hpv.information10) Information10, ' ||
826 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION11'',hpv.information11) Information11, ' ||
827 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION12'',hpv.information12) Information12, ' ||
828 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION13'',hpv.information13) Information13, ' ||
829 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION14'',hpv.information14) Information14, ' ||
830 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION15'',hpv.information15) Information15, ' ||
831 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION16'',hpv.information16) Information16, ' ||
832 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION17'',hpv.information17) Information17, ' ||
833 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION18'',hpv.information18) Information18, ' ||
834 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION19'',hpv.information19) Information19, ' ||
835 ' per_ri_rt_util_pkg.get_display_value (''Position Developer DF'',hpv.information_category,''INFORMATION20'',hpv.information20) Information20 ' ||
836 ' from hr_positions_v hpv ' ||
837 'where hpv.business_group_id = :business_group_id'; --|| p_business_group_id;
838
839 l_entityset := 'Positions';
840 l_entity := 'Position';
841 ELSIF (p_entity_code = 'POSITION_HIERARCHY') THEN
842 l_query_str := 'select pps.name Name, ' ||
843 ' pps.primary_position_flag PrimaryFlg, ' ||
844 ' psv.version_number Version, ' ||
845 ' to_char(psv.DATE_FROM,''rrrr-mm-dd'') DateFrom, ' ||
846 ' to_char(psv.date_to,''rrrr-mm-dd'') DateTo, ' ||
847 ' pp1.NAME ParentPosition, ' ||
848 ' pp2.name SubordinatePosition ' ||
849 ' from per_pos_structure_elements pse, ' ||
850 ' per_pos_structure_versions psv, ' ||
851 ' per_position_structures pps, ' ||
852 ' per_positions pp1, ' ||
853 ' per_positions pp2 ' ||
854 ' where pse.POS_STRUCTURE_VERSION_ID = psv.POS_STRUCTURE_VERSION_ID ' ||
855 ' and psv.POSITION_STRUCTURE_ID = pps.POSITION_STRUCTURE_ID ' ||
856 ' and pse.PARENT_POSITION_ID = pp1.POSITION_ID ' ||
857 ' and pse.SUBORDINATE_POSITION_ID = pp2.position_id ' ||
858 ' and pse.business_group_id = :business_group_id'; --|| p_business_group_id;
859
860 l_entityset := 'PositionHierarchies';
861 l_entity := 'PositionHierarchy';
862 ELSIF (p_entity_code = 'ELEMENT_LINKS') THEN
863 hr_utility.trace('Element Link.........1');
864 l_query_str := 'select pel.element_name ElementName, ' ||
865 ' pel.description Description, ' ||
866 ' pel.classification_name Classification, ' ||
867 ' pel.processing_type ProcessingType, ' ||
868 ' pel.standard_link_flag StandardLinkFlg, ' ||
869 ' pel.organization_name OrganizationName, ' ||
870 ' pel.job_name Job, ' ||
871 ' pel.grade_name Grade, ' ||
872 ' pel.employment_category_name EmpCategory, ' ||
873 ' pel.pay_basis_name SalaryBasis, ' ||
874 ' ppg.group_name PeopleGroup, ' ||
875 ' pel.position_name Position, ' ||
876 ' pel.location_code Location, ' ||
877 ' pel.payroll_name Payroll, ' ||
878 ' pel.link_to_all_payrolls_flag LinkToAllPayFlg, ' ||
879 ' pel.transfer_to_gl_flag TransferToGL, ' ||
880 ' pel.costable_type CostableType, ' ||
881 ' pel.element_set_name DistributionSet, ' ||
882 ' pel.qualifying_age QualAge, ' ||
883 ' pel.qualifying_length_of_service QualLengthOfService, ' ||
884 ' pel.qualifying_unit_name QualUnits, ' ||
885 ' to_char(pel.effective_start_date,''rrrr-mm-dd'') EffectiveStartDate, ' ||
886 ' decode(to_char(pel.Effective_end_date,''rrrr-mm-dd''),''4712-12-31'',null,to_char(effective_end_date,''rrrr-mm-dd'')) EffectiveEndDate ' ||
887 ' from pay_element_links_v pel, ' ||
888 ' pay_people_groups ppg ' ||
889 ' where pel.PEOPLE_GROUP_ID = ppg.PEOPLE_GROUP_ID(+) ' ||
890 ' and pel.business_group_id = :business_group_id'; --|| p_business_group_id;
891
892 l_entityset := 'ElementLinks';
893 l_entity := 'ElementLink';
894 ELSIF (p_entity_code = 'EMPLOYEES_US') THEN
895 l_query_str := 'select ppv.EMPLOYEE_NUMBER EmployeeNumber, ' ||
896 ' ppv.LAST_NAME LastName, ' ||
897 ' ppv.FIRST_NAME FirstName, ' ||
898 ' ppv.D_TITLE Title, ' ||
899 ' ppv.PRE_NAME_ADJUNCT Prefix, ' ||
900 ' ppv.SUFFIX Suffix, ' ||
901 ' ppv.MIDDLE_NAMES MiddleName, ' ||
902 ' ppv.D_SEX Sex, ' ||
903 ' ppv.D_PERSON_TYPE_ID PersonType, ' ||
904 ' ppv.NATIONAL_IDENTIFIER SSNNumber, ' ||
905 ' to_char(ppv.DATE_OF_BIRTH,''rrrr-mm-dd'') DateOfBirth, ' ||
906 ' ppv.TOWN_OF_BIRTH TownOfBirth, ' ||
907 ' ppv.REGION_OF_BIRTH RegionOfBirth, ' ||
908 ' ppv.D_COUNTRY_OF_BIRTH CountryOfBirth, ' ||
909 ' ppv.D_MARITAL_STATUS MaritalStatus, ' ||
910 ' ppv.D_NATIONALITY Nationality, ' ||
911 ' ppv.D_REGISTERED_DISABLED_FLAG RegisteredDisabled, ' ||
912 ' to_char(ppv.EFFECTIVE_START_DATE,''rrrr-mm-dd'') EffectiveStartDate, ' ||
913 ' to_char(ppv.EFFECTIVE_END_DATE,''rrrr-mm-dd'') EffectiveEndDate, ' ||
914 ' to_char(ppv.HIRE_DATE,''rrrr-mm-dd'') LatestStartDate, ' ||
915 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION1'',per_information1) EthnicOrigin, ' ||
916 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION2'',per_information2) I_9, ' ||
917 ' to_char(to_date(per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION3'',per_information3),''rrrr-mm-dd hh24:mi:ss''),''rrrr-mm-dd'') I_9ExpDate, ' ||
918 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION5'',per_information5) VeteranStatus, ' ||
919 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION7'',per_information7) NewHireStatus, ' ||
920 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION8'',per_information8) ReasonForExclusion, ' ||
921 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION9'',per_information9) ChildSupportObligation, ' ||
922 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION10'',per_information10) OptedforMedicarePartB, ' ||
923 ' ppv.OFFICE_NUMBER Office, ' ||
924 ' ppv.INTERNAL_LOCATION Location, ' ||
925 ' ppv.EMAIL_ADDRESS EmailId, ' ||
926 ' ppv.MAILSTOP MailStop, ' ||
927 ' ppv.D_HOME_OFFICE MailTo, ' ||
928 ' ppv.RESUME_EXISTS ResumeExists, ' ||
929 ' to_char(ppv.RESUME_LAST_UPDATED,''rrrr-mm-dd'') ResumeLastUpdated, ' ||
930 ' to_char(ppv.HOLD_APPLICANT_DATE_UNTIL,''rrrr-mm-dd'') HoldApplDateUntil, ' ||
931 ' ppv.HONORS Honors, ' ||
932 ' ppv.KNOWN_AS PreferredName, ' ||
933 ' ppv.PREVIOUS_LAST_NAME PreviousLastName, ' ||
934 ' ppv.D_WORK_SCHEDULE WorkSchedule, ' ||
935 ' ppv.D_STUDENT_STATUS StudentStatus, ' ||
936 ' ppv.FTE_CAPACITY FullTimeAvailability, ' ||
937 ' to_char(ppv.DATE_EMPLOYEE_DATA_VERIFIED,''rrrr-mm-dd'') DateLastVerified, ' ||
938 ' ppv.D_CORR_LANGUAGE CorrespendenceLang, ' ||
939 ' ppv.ON_MILITARY_SERVICE OnMilitaryService, ' ||
940 ' ppv.SECOND_PASSPORT_EXISTS SecondPassportExists, ' ||
941 ' to_char(ppv.DATE_OF_DEATH,''rrrr-mm-dd'') DateOfDeath, ' ||
942 ' ppv.D_BENEFIT_GROUP_NAME BenefitGroupName, ' ||
943 ' ppv.D_USES_TOBACCO_FLAG UsesTobacco, ' ||
944 ' to_char(ppv.ADJUSTED_SVC_DATE,''rrrr-mm-dd'') AdjustedSrvcDate, ' ||
945 ' to_char(ppv.DPDNT_ADOPTION_DATE,''rrrr-mm-dd'') AdoptionDate, ' ||
946 ' to_char(ppv.ORIGINAL_DATE_OF_HIRE,''rrrr-mm-dd'') DateFirstHired ' ||
947 ' from per_people_v ppv ' ||
948 ' where ppv.S_SYSTEM_PERSON_TYPE = ''EMP'' ' ||
949 ' and business_group_id = :business_group_id'; --|| p_business_group_id;
950
951 l_entityset := 'Employees';
952 l_entity := 'Employee';
953 ELSIF (p_entity_code = 'EMPLOYEES_GB') THEN
954
955 l_query_str := 'select ppv.EMPLOYEE_NUMBER EmployeeNumber, ' ||
956 ' ppv.LAST_NAME LastName, ' ||
957 ' ppv.FIRST_NAME FirstName, ' ||
958 ' ppv.D_TITLE Title, ' ||
959 ' ppv.PRE_NAME_ADJUNCT Prefix, ' ||
960 ' ppv.SUFFIX Suffix, ' ||
961 ' ppv.MIDDLE_NAMES MiddleName, ' ||
962 ' ppv.D_SEX Sex, ' ||
963 ' ppv.D_PERSON_TYPE_ID PersonType, ' ||
964 ' ppv.NATIONAL_IDENTIFIER SSNNumber, ' ||
965 ' to_char(ppv.DATE_OF_BIRTH,''rrrr-mm-dd'') DateOfBirth, ' ||
966 ' ppv.TOWN_OF_BIRTH TownOfBirth, ' ||
967 ' ppv.REGION_OF_BIRTH RegionOfBirth, ' ||
968 ' ppv.D_COUNTRY_OF_BIRTH CountryOfBirth, ' ||
969 ' ppv.D_MARITAL_STATUS MaritalStatus, ' ||
970 ' ppv.D_NATIONALITY Nationality, ' ||
971 ' ppv.D_REGISTERED_DISABLED_FLAG RegisteredDisabled, ' ||
972 ' to_char(ppv.EFFECTIVE_START_DATE,''rrrr-mm-dd'') EffectiveStartDate, ' ||
973 ' to_char(ppv.EFFECTIVE_END_DATE,''rrrr-mm-dd'') EffectiveEndDate, ' ||
974 ' to_char(ppv.HIRE_DATE,''rrrr-mm-dd'') LatestStartDate, ' ||
975 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION1'',per_information1) EthnicOrigin, ' ||
976 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION2'',per_information2) Director, ' ||
977 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION3'',per_information3) NoOfWeeks, ' ||
978 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION4'',per_information4) Pensioner, ' ||
979 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION5'',per_information5) WorkPermitNo, ' ||
980 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION6'',per_information6) AdditionalPensionableYears, ' ||
981 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION7'',per_information7) AdditionalPensionableMonths, ' ||
982 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION8'',per_information8) AdditionalPensionableDays, ' ||
983 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION9'',per_information9) NIMultipleAssignments, ' ||
984 ' per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION10'',per_information10) PAYEAggregatedAssignments, ' ||
985 ' to_char(to_date(per_ri_rt_util_pkg.get_display_value (''Person Developer DF'',ppv.per_information_category,''PER_INFORMATION11'',per_information11),''rrrr-mm-dd hh24:mi:ss''),''rrrr-mm-dd'') DSSLinkingLetterEndDate, ' ||
986 ' ppv.OFFICE_NUMBER Office, ' ||
987 ' ppv.INTERNAL_LOCATION Location, ' ||
988 ' ppv.EMAIL_ADDRESS EmailId, ' ||
989 ' ppv.MAILSTOP MailStop, ' ||
990 ' ppv.D_HOME_OFFICE MailTo, ' ||
991 ' ppv.RESUME_EXISTS ResumeExists, ' ||
992 ' to_char(ppv.RESUME_LAST_UPDATED,''rrrr-mm-dd'') ResumeLastUpdated, ' ||
993 ' to_char(ppv.HOLD_APPLICANT_DATE_UNTIL,''rrrr-mm-dd'') HoldApplDateUntil, ' ||
994 ' ppv.HONORS Honors, ' ||
995 ' ppv.KNOWN_AS PreferredName, ' ||
996 ' ppv.PREVIOUS_LAST_NAME PreviousLastName, ' ||
997 ' ppv.D_WORK_SCHEDULE WorkSchedule, ' ||
998 ' ppv.D_STUDENT_STATUS StudentStatus, ' ||
999 ' ppv.FTE_CAPACITY FullTimeAvailability, ' ||
1000 ' to_char(ppv.DATE_EMPLOYEE_DATA_VERIFIED,''rrrr-mm-dd'') DateLastVerified, ' ||
1001 ' ppv.D_CORR_LANGUAGE CorrespendenceLang, ' ||
1002 ' ppv.ON_MILITARY_SERVICE OnMilitaryService, ' ||
1003 ' ppv.SECOND_PASSPORT_EXISTS SecondPassportExists, ' ||
1004 ' to_char(ppv.DATE_OF_DEATH,''rrrr-mm-dd'') DateOfDeath, ' ||
1005 ' ppv.D_BENEFIT_GROUP_NAME BenefitGroupName, ' ||
1006 ' ppv.D_USES_TOBACCO_FLAG UsesTobacco, ' ||
1007 ' to_char(ppv.ADJUSTED_SVC_DATE,''rrrr-mm-dd'') AdjustedSrvcDate, ' ||
1008 ' to_char(ppv.DPDNT_ADOPTION_DATE,''rrrr-mm-dd'') AdoptionDate, ' ||
1009 ' to_char(ppv.ORIGINAL_DATE_OF_HIRE,''rrrr-mm-dd'') DateFirstHired ' ||
1010 ' from per_people_v ppv ' ||
1011 ' where ppv.S_SYSTEM_PERSON_TYPE = ''EMP'' ' ||
1012 ' and business_group_id = :business_group_id'; --|| p_business_group_id;
1013
1014 l_entityset := 'Employees';
1015 l_entity := 'Employee';
1016 ELSIF (p_entity_code = 'EMPLOYEES') THEN
1017 l_query_str := 'select ppv.EMPLOYEE_NUMBER EmployeeNumber, ' ||
1018 ' ppv.LAST_NAME LastName, ' ||
1019 ' ppv.FIRST_NAME FirstName, ' ||
1020 ' ppv.D_TITLE Title, ' ||
1021 ' ppv.PRE_NAME_ADJUNCT Prefix, ' ||
1022 ' ppv.SUFFIX Suffix, ' ||
1023 ' ppv.MIDDLE_NAMES MiddleName, ' ||
1024 ' ppv.D_SEX Sex, ' ||
1025 ' ppv.D_PERSON_TYPE_ID PersonType, ' ||
1026 ' ppv.NATIONAL_IDENTIFIER SSNNumber, ' ||
1027 ' to_char(ppv.DATE_OF_BIRTH,''rrrr-mm-dd'') DateOfBirth, ' ||
1028 ' ppv.TOWN_OF_BIRTH TownOfBirth, ' ||
1029 ' ppv.REGION_OF_BIRTH RegionOfBirth, ' ||
1030 ' ppv.D_COUNTRY_OF_BIRTH CountryOfBirth, ' ||
1031 ' ppv.D_MARITAL_STATUS MaritalStatus, ' ||
1032 ' ppv.D_NATIONALITY Nationality, ' ||
1033 ' ppv.D_REGISTERED_DISABLED_FLAG RegisteredDisabled, ' ||
1034 ' to_char(ppv.EFFECTIVE_START_DATE,''rrrr-mm-dd'') EffectiveStartDate, ' ||
1035 ' to_char(ppv.EFFECTIVE_END_DATE,''rrrr-mm-dd'') EffectiveEndDate, ' ||
1036 ' to_char(ppv.HIRE_DATE,''rrrr-mm-dd'') LatestStartDate, ' ||
1037 ' ppv.OFFICE_NUMBER Office, ' ||
1038 ' ppv.INTERNAL_LOCATION Location, ' ||
1039 ' ppv.EMAIL_ADDRESS EmailId, ' ||
1040 ' ppv.MAILSTOP MailStop, ' ||
1041 ' ppv.D_HOME_OFFICE MailTo, ' ||
1042 ' ppv.RESUME_EXISTS ResumeExists, ' ||
1043 ' to_char(ppv.RESUME_LAST_UPDATED,''rrrr-mm-dd'') ResumeLastUpdated, ' ||
1044 ' to_char(ppv.HOLD_APPLICANT_DATE_UNTIL,''rrrr-mm-dd'') HoldApplDateUntil, ' ||
1045 ' ppv.HONORS Honors, ' ||
1046 ' ppv.KNOWN_AS PreferredName, ' ||
1047 ' ppv.PREVIOUS_LAST_NAME PreviousLastName, ' ||
1048 ' ppv.D_WORK_SCHEDULE WorkSchedule, ' ||
1049 ' ppv.D_STUDENT_STATUS StudentStatus, ' ||
1050 ' ppv.FTE_CAPACITY FullTimeAvailability, ' ||
1051 ' to_char(ppv.DATE_EMPLOYEE_DATA_VERIFIED,''rrrr-mm-dd'') DateLastVerified, ' ||
1052 ' ppv.D_CORR_LANGUAGE CorrespendenceLang, ' ||
1053 ' ppv.ON_MILITARY_SERVICE OnMilitaryService, ' ||
1054 ' ppv.SECOND_PASSPORT_EXISTS SecondPassportExists, ' ||
1055 ' to_char(ppv.DATE_OF_DEATH,''rrrr-mm-dd'') DateOfDeath, ' ||
1056 ' ppv.D_BENEFIT_GROUP_NAME BenefitGroupName, ' ||
1057 ' ppv.D_USES_TOBACCO_FLAG UsesTobacco, ' ||
1058 ' to_char(ppv.ADJUSTED_SVC_DATE,''rrrr-mm-dd'') AdjustedSrvcDate, ' ||
1059 ' to_char(ppv.DPDNT_ADOPTION_DATE,''rrrr-mm-dd'') AdoptionDate, ' ||
1060 ' to_char(ppv.ORIGINAL_DATE_OF_HIRE,''rrrr-mm-dd'') DateFirstHired ' ||
1061 ' from per_people_v ppv ' ||
1062 ' where ppv.S_SYSTEM_PERSON_TYPE = ''EMP'' ' ||
1063 ' and business_group_id = :business_group_id'; --|| p_business_group_id;
1064
1065 l_entityset := 'Employees';
1066 l_entity := 'Employee';
1067 ELSIF (p_entity_code = 'BUSINESS_GROUPS') THEN
1068 l_query_str := 'select hou.NAME Name, ' ||
1069 ' hou.ORGANIZATION_TYPE OrganiationType, ' ||
1070 ' to_char(hou.DATE_FROM,''rrrr-mm-dd'') DateFrom, ' ||
1071 ' to_char(hou.Date_to,''rrrr-mm-dd'') DateTo, ' ||
1072 ' hou.LOCATION_CODE Location, ' ||
1073 ' hou.INTERNAL_EXTERNAL_MEANING Internal, ' ||
1074 ' hou.INTERNAL_ADDRESS_LINE InternalAddress, ' ||
1075 ' hoi1.ORG_INFORMATION1_MEANING Classification, ' ||
1076 ' hoi1.org_information1 Enabled, ' ||
1077 ' hoi.ORG_INFORMATION_CONTEXT AdditionalOrgInfo, ' ||
1078 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION1'') OrgInfoPmt1, ' ||
1079 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION2'') OrgInfoPmt2, ' ||
1080 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION3'') OrgInfoPmt3, ' ||
1081 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION4'') OrgInfoPmt4, ' ||
1082 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION5'') OrgInfoPmt5, ' ||
1083 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION6'') OrgInfoPmt6, ' ||
1084 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION7'') OrgInfoPmt7, ' ||
1085 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION8'') OrgInfoPmt8, ' ||
1086 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION9'') OrgInfoPmt9, ' ||
1087 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION10'') OrgInfoPmt10, ' ||
1088 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION11'') OrgInfoPmt11, ' ||
1089 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION12'') OrgInfoPmt12, ' ||
1090 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION13'') OrgInfoPmt13, ' ||
1091 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION14'') OrgInfoPmt14, ' ||
1092 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION15'') OrgInfoPmt15, ' ||
1093 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION16'') OrgInfoPmt16, ' ||
1094 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION17'') OrgInfoPmt17, ' ||
1095 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION18'') OrgInfoPmt18, ' ||
1096 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION19'') OrgInfoPmt19, ' ||
1097 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION20'') OrgInfoPmt20, ' ||
1098 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION1'',hoi.org_information1) OrgInfo1, ' ||
1099 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION2'',hoi.org_information2) OrgInfo2, ' ||
1100 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION3'',hoi.org_information3) OrgInfo3, ' ||
1101 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION4'',hoi.org_information4) OrgInfo4, ' ||
1102 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION5'',hoi.org_information5) OrgInfo5, ' ||
1103 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION6'',hoi.org_information6) OrgInfo6, ' ||
1104 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION7'',hoi.org_information7) OrgInfo7, ' ||
1105 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION8'',hoi.org_information8) OrgInfo8, ' ||
1106 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION9'',hoi.org_information9) OrgInfo9, ' ||
1107 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION10'',hoi.org_information10) OrgInfo10, ' ||
1108 ' to_char(to_date(per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION11'',hoi.org_information11),''rrrr/mm/dd hh24:mi:ss''),''rrrr-mm-dd'') OrgInfo11, ' ||
1109 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION12'',hoi.org_information12) OrgInfo12, ' ||
1110 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION13'',hoi.org_information13) OrgInfo13, ' ||
1111 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION14'',hoi.org_information14) OrgInfo14, ' ||
1112 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION15'',hoi.org_information15) OrgInfo15, ' ||
1113 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION16'',hoi.org_information16) OrgInfo16, ' ||
1114 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION17'',hoi.org_information17) OrgInfo17, ' ||
1115 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION18'',hoi.org_information18) OrgInfo18, ' ||
1116 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION19'',hoi.org_information19) OrgInfo19, ' ||
1117 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION20'',hoi.org_information20) OrgInfo20 ' ||
1118 ' from hr_organization_information_v hoi, ' ||
1119 ' hr_organization_information_v hoi1, ' ||
1120 ' hr_organization_units_v hou ' ||
1121 ' where hoi.organization_id = hou.ORGANIZATION_ID ' ||
1122 ' and hoi1.organization_id = hou.organization_id ' ||
1123 ' and hoi.organization_id = hoi1.organization_id ' ||
1124 ' and hoi1.org_information1 = ''HR_BG'' '||
1125 ' and hoi.ORG_INFORMATION_CONTEXT = ''Business Group Information'' ' ||
1126 ' and hou.business_group_id = :business_group_id'; --|| p_business_group_id;
1127 l_entityset := 'BusinessGroups';
1128 l_entity := 'BusinessGroup';
1129 ELSIF (p_entity_code = 'LEGAL_ENTITIES') THEN
1130 l_query_str := 'select hou.NAME Name, ' ||
1131 ' hou.ORGANIZATION_TYPE OrganiationType, ' ||
1132 ' to_char(hou.DATE_FROM,''rrrr-mm-dd'') DateFrom, ' ||
1133 ' to_char(hou.Date_to,''rrrr-mm-dd'') DateTo, ' ||
1134 ' hou.LOCATION_CODE Location, ' ||
1135 ' hou.INTERNAL_EXTERNAL_MEANING Internal, ' ||
1136 ' hou.INTERNAL_ADDRESS_LINE InternalAddress, ' ||
1137 ' hoi1.ORG_INFORMATION1_MEANING Classification, ' ||
1138 ' hoi1.org_information1 Enabled, ' ||
1139 ' hoi.ORG_INFORMATION_CONTEXT AdditionalOrgInfo, ' ||
1140 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION1'') OrgInfoPmt1, ' ||
1141 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION2'') OrgInfoPmt2, ' ||
1142 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION3'') OrgInfoPmt3, ' ||
1143 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION4'') OrgInfoPmt4, ' ||
1144 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION5'') OrgInfoPmt5, ' ||
1145 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION6'') OrgInfoPmt6, ' ||
1146 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION7'') OrgInfoPmt7, ' ||
1147 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION8'') OrgInfoPmt8, ' ||
1148 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION9'') OrgInfoPmt9, ' ||
1149 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION10'') OrgInfoPmt10, ' ||
1150 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION11'') OrgInfoPmt11, ' ||
1151 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION12'') OrgInfoPmt12, ' ||
1152 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION13'') OrgInfoPmt13, ' ||
1153 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION14'') OrgInfoPmt14, ' ||
1154 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION15'') OrgInfoPmt15, ' ||
1155 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION16'') OrgInfoPmt16, ' ||
1156 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION17'') OrgInfoPmt17, ' ||
1157 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION18'') OrgInfoPmt18, ' ||
1158 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION19'') OrgInfoPmt19, ' ||
1159 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION20'') OrgInfoPmt20, ' ||
1160 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION1'',hoi.org_information1) OrgInfo1, ' ||
1161 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION2'',hoi.org_information2) OrgInfo2, ' ||
1162 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION3'',hoi.org_information3) OrgInfo3, ' ||
1163 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION4'',hoi.org_information4) OrgInfo4, ' ||
1164 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION5'',hoi.org_information5) OrgInfo5, ' ||
1165 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION6'',hoi.org_information6) OrgInfo6, ' ||
1166 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION7'',hoi.org_information7) OrgInfo7, ' ||
1167 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION8'',hoi.org_information8) OrgInfo8, ' ||
1168 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION9'',hoi.org_information9) OrgInfo9, ' ||
1169 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION10'',hoi.org_information10) OrgInfo10, ' ||
1170 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION11'',hoi.org_information11) OrgInfo11, ' ||
1171 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION12'',hoi.org_information12) OrgInfo12, ' ||
1172 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION13'',hoi.org_information13) OrgInfo13, ' ||
1173 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION14'',hoi.org_information14) OrgInfo14, ' ||
1174 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION15'',hoi.org_information15) OrgInfo15, ' ||
1175 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION16'',hoi.org_information16) OrgInfo16, ' ||
1176 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION17'',hoi.org_information17) OrgInfo17, ' ||
1177 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION18'',hoi.org_information18) OrgInfo18, ' ||
1178 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION19'',hoi.org_information19) OrgInfo19, ' ||
1179 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION20'',hoi.org_information20) OrgInfo20 ' ||
1180 ' from hr_organization_information_v hoi, ' ||
1181 ' hr_organization_information_v hoi1, ' ||
1182 ' hr_organization_units_v hou ' ||
1183 ' where hoi.organization_id = hou.ORGANIZATION_ID ' ||
1184 ' and hoi1.organization_id = hou.organization_id ' ||
1185 ' and hoi.organization_id = hoi1.organization_id ' ||
1186 ' and hoi1.org_information1 = ''HR_LEGAL'' '||
1187 ' and hoi.ORG_INFORMATION_CONTEXT = ''Legal Entity Accounting'' ' ||
1188 ' and hou.business_group_id = :business_group_id'; --|| p_business_group_id;
1189 l_entityset := 'LegalEntities';
1190 l_entity := 'LegalEntity';
1191 ELSIF (p_entity_code = 'OPERATING_UNITS') THEN
1192 l_query_str := 'select hou.NAME Name, ' ||
1193 ' hou.ORGANIZATION_TYPE OrganiationType, ' ||
1194 ' to_char(hou.DATE_FROM,''rrrr-mm-dd'') DateFrom, ' ||
1195 ' to_char(hou.Date_to,''rrrr-mm-dd'') DateTo, ' ||
1196 ' hou.LOCATION_CODE Location, ' ||
1197 ' hou.INTERNAL_EXTERNAL_MEANING Internal, ' ||
1198 ' hou.INTERNAL_ADDRESS_LINE InternalAddress, ' ||
1199 ' hoi1.ORG_INFORMATION1_MEANING Classification, ' ||
1200 ' hoi1.org_information1 Enabled, ' ||
1201 ' hoi.ORG_INFORMATION_CONTEXT AdditionalOrgInfo, ' ||
1202 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION1'') OrgInfoPmt1, ' ||
1203 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION2'') OrgInfoPmt2, ' ||
1204 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION3'') OrgInfoPmt3, ' ||
1205 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION4'') OrgInfoPmt4, ' ||
1206 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION5'') OrgInfoPmt5, ' ||
1207 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION6'') OrgInfoPmt6, ' ||
1208 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION7'') OrgInfoPmt7, ' ||
1209 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION8'') OrgInfoPmt8, ' ||
1210 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION9'') OrgInfoPmt9, ' ||
1211 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION10'') OrgInfoPmt10, ' ||
1212 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION11'') OrgInfoPmt11, ' ||
1213 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION12'') OrgInfoPmt12, ' ||
1214 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION13'') OrgInfoPmt13, ' ||
1215 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION14'') OrgInfoPmt14, ' ||
1216 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION15'') OrgInfoPmt15, ' ||
1217 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION16'') OrgInfoPmt16, ' ||
1218 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION17'') OrgInfoPmt17, ' ||
1219 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION18'') OrgInfoPmt18, ' ||
1220 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION19'') OrgInfoPmt19, ' ||
1221 ' per_ri_rt_util_pkg.get_display_prompt(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION20'') OrgInfoPmt20, ' ||
1222 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION1'',hoi.org_information1) OrgInfo1, ' ||
1223 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION2'',hoi.org_information2) OrgInfo2, ' ||
1224 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION3'',hoi.org_information3) OrgInfo3, ' ||
1225 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION4'',hoi.org_information4) OrgInfo4, ' ||
1226 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION5'',hoi.org_information5) OrgInfo5, ' ||
1227 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION6'',hoi.org_information6) OrgInfo6, ' ||
1228 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION7'',hoi.org_information7) OrgInfo7, ' ||
1229 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION8'',hoi.org_information8) OrgInfo8, ' ||
1230 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION9'',hoi.org_information9) OrgInfo9, ' ||
1231 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION10'',hoi.org_information10) OrgInfo10, ' ||
1232 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION11'',hoi.org_information11) OrgInfo11, ' ||
1233 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION12'',hoi.org_information12) OrgInfo12, ' ||
1234 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION13'',hoi.org_information13) OrgInfo13, ' ||
1235 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION14'',hoi.org_information14) OrgInfo14, ' ||
1236 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION15'',hoi.org_information15) OrgInfo15, ' ||
1237 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION16'',hoi.org_information16) OrgInfo16, ' ||
1238 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION17'',hoi.org_information17) OrgInfo17, ' ||
1239 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION18'',hoi.org_information18) OrgInfo18, ' ||
1240 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION19'',hoi.org_information19) OrgInfo19, ' ||
1241 ' per_ri_rt_util_pkg.GET_DISPLAY_VALUE(''Org Developer DF'',hoi.org_information_CONTEXT,''ORG_INFORMATION20'',hoi.org_information20) OrgInfo20 ' ||
1242 ' from hr_organization_information_v hoi, ' ||
1243 ' hr_organization_information_v hoi1, ' ||
1244 ' hr_organization_units_v hou ' ||
1245 ' where hoi.organization_id = hou.ORGANIZATION_ID ' ||
1246 ' and hoi1.organization_id = hou.organization_id ' ||
1247 ' and hoi.organization_id = hoi1.organization_id ' ||
1248 ' and hoi1.org_information1 = ''OPERATING_UNIT'' '||
1249 ' and hoi.ORG_INFORMATION_CONTEXT = ''Operating Unit Information'' ' ||
1250 ' and hou.business_group_id = :business_group_id'; --|| p_business_group_id;
1251 l_entityset := 'OperatingUnits';
1252 l_entity := 'OperatingUnit';
1253 ELSE
1254 p_xmldata := null;
1255 RETURN;
1256 END IF;
1257 hr_utility.trace(l_entityset || '.........2');
1258 --hr_utility.trace('Query is ' || l_query_str);
1259
1260 queryCtx := DBMS_XMLQuery.newContext(l_query_str);
1261 DBMS_XMLQuery.setMaxRows(queryCtx,l_sample_size);
1262 DBMS_XMLQuery.setRowsetTag(queryctx,l_entityset);
1263
1264 hr_utility.trace(l_entityset || '.........3');
1265 DBMS_XMLQuery.setRowTag(queryctx,l_entityset);
1266
1267 DBMS_XMLQuery.setBindValue(queryCtx,'business_group_id',p_business_group_id);
1268
1269 p_xmldata := DBMS_XMLQuery.getXML(queryCtx);
1270 hr_utility.trace(l_entityset || '.........4');
1271
1272 IF (instr(p_xmldata,(l_entityset || '/')) <>0 ) THEN
1273 p_xmldata := null;
1274 END IF;
1275 hr_utility.trace('Returned' );
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 hr_utility.trace(substr(SQLERRM,1,1000));
1279 hr_utility.trace(l_entityset || '.........5');
1280 RAISE;
1281 END generate_xml;
1282 PROCEDURE apps_initialise IS
1283 begin
1284 insert into fnd_sessions(session_id,effective_date) values (userenv('SESSIONID'),sysdate);
1285 exception
1286 when others then
1287 hr_utility.trace(substr(SQLERRM,1,100));
1288 raise;
1289 end;
1290 END per_ri_rt_util_pkg;