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

Source


1 package body HR_JP_ID_PKG as
2 /* $Header: hrjpid.pkb 120.0 2005/05/30 20:58:09 appldev noship $ */
3 	TYPE UnqColRecTyp IS RECORD(
4 			name			VARCHAR2(255),
5 			value			VARCHAR2(255));
6 	TYPE UniColTbl IS TABLE OF UnqColRecTyp INDEX BY BINARY_INTEGER;
7 	TYPE RefCsr IS REF CURSOR;
8 	C_CHR_LF		CONSTANT VARCHAR2(5) := fnd_global.local_chr(10);
9 	g_bg_rec_cache		PER_BUSINESS_GROUPS%ROWTYPE;
10 	g_sql			VARCHAR2(32767);
11 --------------------------------------------------------------------------------
12 	FUNCTION LATEST_SQL RETURN VARCHAR2
13 --------------------------------------------------------------------------------
14 	IS
15 	BEGIN
16 		return g_sql;
17 	END;
18 --------------------------------------------------------------------------------
19 -- Internal Functions
20 --------------------------------------------------------------------------------
21 -- In case of DATETRACKED and DATED with p_effective_date is NULL,
22 -- this function returns multiple records(cursor variable).
23 -- If you want to return unique record with DATETRACKED and DATED,
24 -- pass p_effective_date with non-null value.
25 	FUNCTION CSR(
26 			p_base_table			IN VARCHAR2,
27 			p_unique_column_tbl		IN UniColTbl,
28 			p_date_track_type		IN VARCHAR2	DEFAULT 'NONE',--'DATETRACKED','DATED','NONE'
29 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys) RETURN RefCsr
30 --------------------------------------------------------------------------------
31 	IS
32 		l_csr			RefCsr;
33 		l_id			NUMBER;
34 		l_index			NUMBER;
35 		l_where_prefix		VARCHAR2(10) := 'where	';
36 		l_operand		VARCHAR2(10);
37 	BEGIN
38 		g_sql	:= 'select * from ' || p_base_table;
39 
40 		l_index := p_unique_column_tbl.first;
41 		while l_index is not NULL loop
42 			if p_unique_column_tbl(l_index).value is NULL then
43 				l_operand := ' is ';
44 			else
45 				l_operand := ' = ';
46 			end if;
47 
48 			g_sql :=	g_sql || C_CHR_LF ||
49 					l_where_prefix || p_unique_column_tbl(l_index).name || l_operand || nvl(p_unique_column_tbl(l_index).value,'NULL');
50 
51 			l_where_prefix := 'and	';
52 			l_index := p_unique_column_tbl.next(l_index);
53 		end loop;
54 
55 		BEGIN
56 			-- In case of DATETRACKED and DATED with effective_date is NULL,
57 			-- it's possibly returns multiple records.
58 			if p_date_track_type = 'DATETRACKED' then
59 				if p_effective_date is not NULL then
60 					g_sql :=	g_sql || C_CHR_LF ||
61 							l_where_prefix || ':b_effective_date between effective_start_date and effective_end_date' || C_CHR_LF ||
62 							'order by effective_start_date';
63 					open l_csr for g_sql using p_effective_date;
64 				else
65 					g_sql :=	g_sql || C_CHR_LF ||
66 							'order by effective_start_date';
67 					open l_csr for g_sql;
68 				end if;
69 			elsif p_date_track_type = 'DATED' then
70 				if p_effective_date is not NULL then
71 					g_sql :=	g_sql || C_CHR_LF ||
72 							l_where_prefix || ':b_effective_date between date_from and nvl(date_to,:b_effective_date)' || C_CHR_LF ||
73 							'order by date_from';
74 					open l_csr for g_sql using p_effective_date,p_effective_date;
75 				else
76 					g_sql :=	g_sql || C_CHR_LF ||
77 							'order by date_from';
78 					open l_csr for g_sql;
79 				end if;
80 			else
81 				open l_csr for g_sql;
82 			end if;
83 		EXCEPTION
84 			when others then
85 				fnd_message.set_name('PER','HR_JP_INVALID_SQL_STATMENT');
86 				fnd_message.set_token('SQL',g_sql);
87 				fnd_message.raise_error;
88 		END;
89 
90 		return l_csr;
91 	END;
92 --------------------------------------------------------------------------------
93 	FUNCTION csr_found_and_close(
94 			p_csr			IN RefCsr,
95 			p_error_when_not_exist	IN VARCHAR2 DEFAULT 'TRUE') RETURN BOOLEAN
96 --------------------------------------------------------------------------------
97 	IS
98 		l_found	BOOLEAN;
99 	BEGIN
100 		if p_csr%NOTFOUND then
101 			l_found := FALSE;
102 			if p_error_when_not_exist = 'TRUE' then
103 				close p_csr;
104 				fnd_message.set_name('PER','HR_JP_ID_NOT_FOUND');
105 				fnd_message.set_token('SQL',g_sql);
106 				fnd_message.raise_error;
107 			end if;
108 		else
109 			l_found := TRUE;
110 		end if;
111 		close p_csr;
112 
113 		return l_found;
114 	END;
115 --------------------------------------------------------------------------------
116 	PROCEDURE unique_tbl_constructor(
117 			p_name				IN VARCHAR2,
118 			p_data_type			IN VARCHAR2,
119 			p_value				IN VARCHAR2,
120 			p_unique_column_tbl		IN OUT NOCOPY UniColTbl)
121 --------------------------------------------------------------------------------
122 	IS
123 		l_index	NUMBER := nvl(p_unique_column_tbl.last,0) + 1;
124 	BEGIN
125 		p_unique_column_tbl(l_index).name := p_name;
126 		if p_value is NULL then
127 			p_unique_column_tbl(l_index).value := NULL;
128 		else
129 			if p_data_type = 'T' then
130 				p_unique_column_tbl(l_index).value	:= '''' || p_value || '''';
131 			elsif p_data_type = 'D' then
132 				p_unique_column_tbl(l_index).value	:= 'to_date(''' || p_value || ''',''YYYY/MM/DD'')';
133 			else
134 				p_unique_column_tbl(l_index).value	:= p_value;
135 			end if;
136 		end if;
137 	END;
138 --------------------------------------------------------------------------------
139 	PROCEDURE bus_leg_constructor(
140 			p_business_group_id		IN NUMBER,
141 			p_legislation_code		IN VARCHAR2,
142 			p_unique_column_tbl		IN OUT NOCOPY UniColTbl)
143 --------------------------------------------------------------------------------
144 	IS
145 		l_legislation_code	VARCHAR2(2);
146 		PROCEDURE raise_error
147 		IS
148 		BEGIN
149 			fnd_message.set_name('PER','HR_JP_INVALID_ARGUMENT');
150 			fnd_message.set_token('PROCEDURE','hr_jp_id_pkg.construct_bus_leg_where_clause');
151 			fnd_message.raise_error;
152 		END;
153 	BEGIN
154 		if p_business_group_id = C_DEFAULT_BUS then
155 			-- p_business_group_id p_legislation_code description
156 			-- -------------------+------------------+------------------------
157 			-- DEFAULTED           DEFAULTED          Available for any BG, LG
158 			-- DEFAULTED           NULL               Error
159 			-- DEFAULTED           JP                 Available for BG in 'JP'
160 			-- -------------------+------------------+------------------------
161 			if p_legislation_code = C_DEFAULT_LEG then
162 				NULL;
163 			elsif p_legislation_code is NULL then
164 				raise_error;
165 			else
166 				unique_tbl_constructor('nvl(nvl(legislation_code,hr_jp_id_pkg.legislation_code(business_group_id,''FALSE'')),''' || p_legislation_code || ''')','T',p_legislation_code,p_unique_column_tbl);
167 			end if;
168 		elsif p_business_group_id is NULL then
169 			-- p_business_group_id p_legislation_code description
170 			-- -------------------+------------------+------------------------
171 			-- NULL                DEFAULTED          Error
172 			-- NULL                JP                 Available for LG='JP'
173 			-- NULL                NULL               As it is
174 			-- -------------------+------------------+------------------------
175 			if p_legislation_code = C_DEFAULT_LEG then
176 				raise_error;
177 			elsif p_legislation_code is not NULL then
178 				unique_tbl_constructor('business_group_id','N',to_char(p_business_group_id),p_unique_column_tbl);
179 				unique_tbl_constructor('nvl(legislation_code,''' || p_legislation_code || ''')','T',p_legislation_code,p_unique_column_tbl);
180 			else
181 				unique_tbl_constructor('business_group_id','N',to_char(p_business_group_id),p_unique_column_tbl);
182 				unique_tbl_constructor('legislation_code','T',p_legislation_code,p_unique_column_tbl);
183 			end if;
184 		else
185 			-- p_business_group_id p_legislation_code description
186 			-- -------------------+------------------+------------------------
187 			-- 101                 NULL               Available for BUG=101
188 			-- 101                 DEFAULTED          Available for BUG=101
189 			-- 101                 JP                 Error
190 			-- -------------------+------------------+------------------------
191 			if nvl(p_legislation_code,C_DEFAULT_LEG) = C_DEFAULT_LEG then
192 				l_legislation_code := legislation_code(p_business_group_id);
193 				unique_tbl_constructor('nvl(business_group_id,' || to_char(p_business_group_id) || ')','N',to_char(p_business_group_id),p_unique_column_tbl);
194 				unique_tbl_constructor('nvl(legislation_code,''' || l_legislation_code || ''')','T',l_legislation_code,p_unique_column_tbl);
195 			else
196 				raise_error;
197 			end if;
198 		end if;
199 	END;
200 --------------------------------------------------------------------------------
201 	FUNCTION CSR_WITH_BUS_LEG(
202 			p_base_table			IN VARCHAR2,
203 			p_unique_column			IN VARCHAR2,
204 			p_unique_column_data_type	IN VARCHAR2 DEFAULT 'T',
205 			p_unique_column_value		IN VARCHAR2,
206 			p_business_group_id		IN NUMBER	DEFAULT NULL,
207 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
208 			p_date_track_type		IN VARCHAR2	DEFAULT 'NONE',
209 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys) RETURN RefCsr
210 --------------------------------------------------------------------------------
211 	IS
212 		l_unique_column_tbl	UniColTbl;
213 	BEGIN
214 		unique_tbl_constructor(p_unique_column,p_unique_column_data_type,p_unique_column_value,l_unique_column_tbl);
215 		bus_leg_constructor(p_business_group_id,p_legislation_code,l_unique_column_tbl);
216 
217 		return csr(p_base_table,l_unique_column_tbl,p_date_track_type,p_effective_date);
218 	END;
219 --------------------------------------------------------------------------------
220 	FUNCTION CSR_WITH_BUS(
221 			p_base_table			IN VARCHAR2,
222 			p_unique_column			IN VARCHAR2,
223 			p_unique_column_data_type	IN VARCHAR2 DEFAULT 'T',
224 			p_unique_column_value		IN VARCHAR2,
225 			p_business_group_id		IN NUMBER,
226 			p_date_track_type		IN VARCHAR2	DEFAULT 'NONE',
227 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys) RETURN RefCsr
228 --------------------------------------------------------------------------------
229 	IS
230 		l_unique_column_tbl	UniColTbl;
231 	BEGIN
232 		unique_tbl_constructor(p_unique_column,p_unique_column_data_type,p_unique_column_value,l_unique_column_tbl);
233 		if p_business_group_id is NULL then
234 			unique_tbl_constructor('business_group_id','N',to_char(p_business_group_id),l_unique_column_tbl);
235 		elsif p_business_group_id <> C_DEFAULT_BUS then
236 			unique_tbl_constructor('nvl(business_group_id,' || to_char(p_business_group_id) || ')','N',to_char(p_business_group_id),l_unique_column_tbl);
237 		end if;
238 
239 		return csr(p_base_table,l_unique_column_tbl,p_date_track_type,p_effective_date);
240 	END;
241 --------------------------------------------------------------------------------
242 	FUNCTION keyflex_combination_id(
243 			p_appl_short_name		IN VARCHAR2,
244 			p_id_flex_code			IN VARCHAR2,
245 			p_id_flex_num			IN NUMBER,
246 			p_concatenated_segments		IN VARCHAR2,
247 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
248 --------------------------------------------------------------------------------
249 	IS
250 		l_valid				BOOLEAN;
251 		l_keyflex_combination_id	NUMBER;
252 	BEGIN
253 		l_valid := fnd_flex_keyval.validate_segs(
254 					OPERATION		=> 'FIND_COMBINATION',
255 					APPL_SHORT_NAME		=> p_appl_short_name,
256 					KEY_FLEX_CODE		=> p_id_flex_code,
257 					STRUCTURE_NUMBER	=> p_id_flex_num,
258 					CONCAT_SEGMENTS		=> p_concatenated_segments,
259 					VALUES_OR_IDS		=> 'I');
260 
261 		if l_valid then
262 			l_keyflex_combination_id := fnd_flex_keyval.combination_id;
263 		else
264 			if p_error_when_not_exist = 'TRUE' then
265 				fnd_message.raise_error;
266 			else
267 				l_keyflex_combination_id := NULL;
268 			end if;
269 		end if;
270 
271 		return l_keyflex_combination_id;
272 	END;
273 --------------------------------------------------------------------------------
274 	FUNCTION business_group_rec(
275 			p_business_group_id		IN NUMBER,
276 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_BUSINESS_GROUPS%ROWTYPE
277 --------------------------------------------------------------------------------
278 	IS
279 		l_unique_column_tbl	UniColTbl;
280 		l_csr			RefCsr;
281 		l_rec			PER_BUSINESS_GROUPS%ROWTYPE;
282 	BEGIN
283 		if p_business_group_id is NULL then
284 			if p_error_when_not_exist = 'TRUE' then
285 				fnd_message.set_name('PER','HR_JP_ID_NOT_FOUND');
286 				fnd_message.raise_error;
287 			end if;
288 
289 			return l_rec;
290 		else
291 			if g_bg_rec_cache.business_group_id = p_business_group_id then
292 				NULL;
293 			else
294 				unique_tbl_constructor('BUSINESS_GROUP_ID','N',to_char(p_business_group_id),l_unique_column_tbl);
295 				l_csr := csr('PER_BUSINESS_GROUPS',l_unique_column_tbl);
296 				fetch l_csr into l_rec;
297 				if not csr_found_and_close(l_csr,p_error_when_not_exist) then
298 					l_rec := NULL;
299 				end if;
300 
301 				g_bg_rec_cache := l_rec;
302 			end if;
303 		end if;
304 
305 		return g_bg_rec_cache;
306 	END;
307 --------------------------------------------------------------------------------
308 	FUNCTION legislation_code(
309 			p_business_group_id		IN NUMBER,
310 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN VARCHAR2
311 --------------------------------------------------------------------------------
312 	IS
313 		l_rec	PER_BUSINESS_GROUPS%ROWTYPE;
314 	BEGIN
315 		l_rec := business_group_rec(p_business_group_id,p_error_when_not_exist);
316 
317 		return l_rec.legislation_code;
318 	END;
319 --------------------------------------------------------------
320 	FUNCTION id_flex_num(
321 			p_business_group_id		IN NUMBER,
322 			p_id_flex_code			IN VARCHAR2,
323 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
324 --------------------------------------------------------------------------------
325 	IS
326 --		POS	Position Key Flexfield(800)
327 --		GRD	Grade Key Flexfield(800)
328 --		JOB	Job Key Flexfield(800)
329 --		COST	Costing Key Flexfield(801)
330 --		GRP	People Group Key Flexfield(801)
331 		l_rec		PER_BUSINESS_GROUPS%ROWTYPE;
332 		l_id_flex_num	NUMBER;
333 	BEGIN
334 		l_rec := business_group_rec(p_business_group_id,p_error_when_not_exist);
335 
336 		if p_id_flex_code = 'POS' then
337 			l_id_flex_num := to_number(l_rec.position_structure);
338 		elsif p_id_flex_code = 'GRD' then
339 			l_id_flex_num := to_number(l_rec.grade_structure);
340 		elsif p_id_flex_code = 'JOB' then
341 			l_id_flex_num := to_number(l_rec.job_structure);
342 		elsif p_id_flex_code = 'COST' then
343 			l_id_flex_num := to_number(l_rec.cost_allocation_structure);
344 		elsif p_id_flex_code = 'GRP' then
345 			l_id_flex_num := to_number(l_rec.people_group_structure);
346 		else
347 			fnd_message.set_name('PER','HR_JP_INVALID_PARAMETER');
348 			fnd_message.set_token('PROCEDURE','hr_jp_id_pkg.id_flex_num');
349 			fnd_message.set_token('PARAMETER',p_id_flex_code);
350 			fnd_message.raise_error;
351 		end if;
352 
353 		return l_id_flex_num;
354 	END;
355 --------------------------------------------------------------------------------
356 	FUNCTION default_currency_code(
357 			p_business_group_id		IN NUMBER,
358 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN VARCHAR2
359 --------------------------------------------------------------------------------
360 	IS
361 		l_rec	PER_BUSINESS_GROUPS%ROWTYPE;
362 	BEGIN
363 		l_rec := business_group_rec(p_business_group_id,p_error_when_not_exist);
364 
365 		return l_rec.currency_code;
366 	END;
367 --------------------------------------------------------------------------------
368 	FUNCTION default_currency_code(
369 			p_legislation_code		IN VARCHAR2,
370 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN VARCHAR2
371 --------------------------------------------------------------------------------
372 	IS
373 		l_currency_code	FND_CURRENCIES.CURRENCY_CODE%TYPE;
374 		CURSOR csr_currency_code IS
375 			select	currency_code
376 			from	fnd_currencies
377 			where	issuing_territory_code = p_legislation_code;
378 	BEGIN
379 		open csr_currency_code;
380 		fetch csr_currency_code into l_currency_code;
381 		if csr_currency_code%NOTFOUND then
382 			if p_error_when_not_exist = 'TRUE' then
383 				close csr_currency_code;
384 				fnd_message.set_name('PER','HR_JP_ID_NOT_FOUND');
385 				fnd_message.raise_error;
386 			end if;
387 		end if;
388 		close csr_currency_code;
389 
390 		return l_currency_code;
391 	END;
392 --------------------------------------------------------------------------------
393 -- ID with BUSINESS_GROUP_ID and LEGISLATION_CODE
394 --------------------------------------------------------------------------------
395 	FUNCTION element_set_rec(
396 			p_element_set_name		IN VARCHAR2,
397 			p_business_group_id		IN NUMBER	DEFAULT NULL,
398 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
399 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_ELEMENT_SETS%ROWTYPE
400 --------------------------------------------------------------------------------
401 	IS
402 		l_csr			RefCsr;
403 		l_rec			PAY_ELEMENT_SETS%ROWTYPE;
404 	BEGIN
405 		l_csr := csr_with_bus_leg('PAY_ELEMENT_SETS','ELEMENT_SET_NAME','T',p_element_set_name,p_business_group_id,p_legislation_code);
406 		fetch l_csr into l_rec;
407 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
408 			l_rec := NULL;
409 		end if;
410 
411 		return l_rec;
412 	END;
413 --------------------------------------------------------------------------------
414 	FUNCTION element_set_id(
415 			p_element_set_name		IN VARCHAR2,
416 			p_business_group_id		IN NUMBER	DEFAULT NULL,
417 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
418 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
419 --------------------------------------------------------------------------------
420 	IS
421 	BEGIN
422 		return element_set_rec(p_element_set_name,p_business_group_id,p_legislation_code,p_error_when_not_exist).element_set_id;
423 	END;
424 --------------------------------------------------------------------------------
425 	FUNCTION backpay_set_rec(
426 			p_backpay_set_name		IN VARCHAR2,
427 			p_business_group_id		IN NUMBER	DEFAULT NULL,
428 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
429 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_BACKPAY_SETS%ROWTYPE
430 --------------------------------------------------------------------------------
431 	IS
432 		l_csr			RefCsr;
433 		l_rec			PAY_BACKPAY_SETS%ROWTYPE;
434 	BEGIN
435 		l_csr := csr_with_bus_leg('PAY_BACKPAY_SETS','BACKPAY_SET_NAME','T',p_backpay_set_name,p_business_group_id,p_legislation_code);
436 		fetch l_csr into l_rec;
437 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
438 			l_rec := NULL;
439 		end if;
440 
441 		return l_rec;
442 	END;
443 --------------------------------------------------------------------------------
444 	FUNCTION backpay_set_id(
445 			p_backpay_set_name		IN VARCHAR2,
446 			p_business_group_id		IN NUMBER	DEFAULT NULL,
447 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
448 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
449 --------------------------------------------------------------------------------
450 	IS
451 	BEGIN
452 		return backpay_set_rec(p_backpay_set_name,p_business_group_id,p_legislation_code,p_error_when_not_exist).backpay_set_id;
453 	END;
454 --------------------------------------------------------------------------------
455 	FUNCTION classification_rec(
456 			p_classification_name		IN VARCHAR2,
457 			p_business_group_id		IN NUMBER	DEFAULT NULL,
458 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
459 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_ELEMENT_CLASSIFICATIONS%ROWTYPE
460 --------------------------------------------------------------------------------
461 	IS
462 		l_csr			RefCsr;
463 		l_rec			PAY_ELEMENT_CLASSIFICATIONS%ROWTYPE;
464 	BEGIN
465 		l_csr := csr_with_bus_leg('PAY_ELEMENT_CLASSIFICATIONS','CLASSIFICATION_NAME','T',p_classification_name,p_business_group_id,p_legislation_code);
466 		fetch l_csr into l_rec;
467 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
468 			l_rec := NULL;
469 		end if;
470 
471 		return l_rec;
472 	END;
473 --------------------------------------------------------------------------------
474 	FUNCTION classification_id(
475 			p_classification_name		IN VARCHAR2,
476 			p_business_group_id		IN NUMBER	DEFAULT NULL,
477 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
478 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
479 --------------------------------------------------------------------------------
480 	IS
481 	BEGIN
482 		return classification_rec(p_classification_name,p_business_group_id,p_legislation_code,p_error_when_not_exist).classification_id;
483 	END;
484 --------------------------------------------------------------------------------
485 	FUNCTION element_type_rec(
486 			p_element_name			IN VARCHAR2,
487 			p_business_group_id		IN NUMBER	DEFAULT NULL,
488 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
489 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
490 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_ELEMENT_TYPES_F%ROWTYPE
491 --------------------------------------------------------------------------------
492 	IS
493 		l_csr			RefCsr;
494 		l_rec			PAY_ELEMENT_TYPES_F%ROWTYPE;
495 	BEGIN
496 		l_csr := csr_with_bus_leg('PAY_ELEMENT_TYPES_F','ELEMENT_NAME','T',p_element_name,p_business_group_id,p_legislation_code,'DATETRACKED',p_effective_date);
497 		fetch l_csr into l_rec;
498 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
499 			l_rec := NULL;
500 		end if;
501 
502 		return l_rec;
503 	END;
504 --------------------------------------------------------------------------------
505 	FUNCTION element_type_id(
506 			p_element_name			IN VARCHAR2,
507 			p_business_group_id		IN NUMBER	DEFAULT NULL,
508 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
509 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
510 --------------------------------------------------------------------------------
511 	IS
512 	BEGIN
513 		return element_type_rec(p_element_name,p_business_group_id,p_legislation_code,NULL,p_error_when_not_exist).element_type_id;
514 	END;
515 --------------------------------------------------------------------------------
516 	FUNCTION balance_type_rec(
517 			p_balance_name			IN VARCHAR2,
518 			p_business_group_id		IN NUMBER	DEFAULT NULL,
519 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
520 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_BALANCE_TYPES%ROWTYPE
521 --------------------------------------------------------------------------------
522 	IS
523 		l_csr			RefCsr;
524 		l_rec			PAY_BALANCE_TYPES%ROWTYPE;
525 	BEGIN
526 		l_csr := csr_with_bus_leg('PAY_BALANCE_TYPES','BALANCE_NAME','T',p_balance_name,p_business_group_id,p_legislation_code);
527 		fetch l_csr into l_rec;
528 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
529 			l_rec := NULL;
530 		end if;
531 
532 		return l_rec;
533 	END;
534 --------------------------------------------------------------------------------
535 	FUNCTION balance_type_id(
536 			p_balance_name			IN VARCHAR2,
537 			p_business_group_id		IN NUMBER	DEFAULT NULL,
538 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
539 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
540 --------------------------------------------------------------------------------
541 	IS
542 	BEGIN
543 		return balance_type_rec(p_balance_name,p_business_group_id,p_legislation_code,p_error_when_not_exist).balance_type_id;
544 	END;
545 --------------------------------------------------------------------------------
546 	FUNCTION balance_dimension_rec(
547 			p_dimension_name		IN VARCHAR2,
548 			p_business_group_id		IN NUMBER	DEFAULT NULL,
549 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
550 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_BALANCE_DIMENSIONS%ROWTYPE
551 --------------------------------------------------------------------------------
552 	IS
553 		l_csr			RefCsr;
554 		l_rec			PAY_BALANCE_DIMENSIONS%ROWTYPE;
555 	BEGIN
556 		l_csr := csr_with_bus_leg('PAY_BALANCE_DIMENSIONS','DIMENSION_NAME','T',p_dimension_name,p_business_group_id,p_legislation_code);
557 		fetch l_csr into l_rec;
558 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
559 			l_rec := NULL;
560 		end if;
561 
562 		return l_rec;
563 	END;
564 --------------------------------------------------------------------------------
565 	FUNCTION balance_dimension_id(
566 			p_dimension_name		IN VARCHAR2,
567 			p_business_group_id		IN NUMBER	DEFAULT NULL,
568 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
569 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
570 --------------------------------------------------------------------------------
571 	IS
572 	BEGIN
573 		return balance_dimension_rec(p_dimension_name,p_business_group_id,p_legislation_code,p_error_when_not_exist).balance_dimension_id;
574 	END;
575 --------------------------------------------------------------------------------
576 	FUNCTION assignment_status_type_rec(
577 			p_user_status			IN VARCHAR2,
578 			p_business_group_id		IN NUMBER	DEFAULT NULL,
579 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
580 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_ASSIGNMENT_STATUS_TYPES%ROWTYPE
581 --------------------------------------------------------------------------------
582 	IS
583 		l_csr			RefCsr;
584 		l_rec			PER_ASSIGNMENT_STATUS_TYPES%ROWTYPE;
585 	BEGIN
586 		l_csr := csr_with_bus_leg('PER_ASSIGNMENT_STATUS_TYPES','USER_STATUS','T',p_user_status,p_business_group_id,p_legislation_code);
587 		fetch l_csr into l_rec;
588 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
589 			l_rec := NULL;
590 		end if;
591 
592 		return l_rec;
593 	END;
594 --------------------------------------------------------------------------------
595 	FUNCTION assignment_status_type_id(
596 			p_user_status			IN VARCHAR2,
597 			p_business_group_id		IN NUMBER	DEFAULT NULL,
598 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
599 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
600 --------------------------------------------------------------------------------
601 	IS
602 	BEGIN
603 		return assignment_status_type_rec(p_user_status,p_business_group_id,p_legislation_code,p_error_when_not_exist).assignment_status_type_id;
604 	END;
605 --------------------------------------------------------------------------------
606 	FUNCTION user_table_rec(
607 			p_user_table_name		IN VARCHAR2,
608 			p_business_group_id		IN NUMBER	DEFAULT NULL,
609 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
610 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_USER_TABLES%ROWTYPE
611 --------------------------------------------------------------------------------
612 	IS
613 		l_csr			RefCsr;
614 		l_rec			PAY_USER_TABLES%ROWTYPE;
615 	BEGIN
616 		l_csr := csr_with_bus_leg('PAY_USER_TABLES','USER_TABLE_NAME','T',p_user_table_name,p_business_group_id,p_legislation_code);
617 		fetch l_csr into l_rec;
618 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
619 			l_rec := NULL;
620 		end if;
621 
622 		return l_rec;
623 	END;
624 --------------------------------------------------------------------------------
625 	FUNCTION user_table_id(
626 			p_user_table_name		IN VARCHAR2,
627 			p_business_group_id		IN NUMBER	DEFAULT NULL,
628 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
629 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
630 --------------------------------------------------------------------------------
631 	IS
632 	BEGIN
633 		return user_table_rec(p_user_table_name,p_business_group_id,p_legislation_code,p_error_when_not_exist).user_table_id;
634 	END;
635 --------------------------------------------------------------------------------
636 -- ID with BUSINESS_GROUP_ID
637 --------------------------------------------------------------------------------
638 	FUNCTION location_rec(
639 			p_location_code			IN VARCHAR2,
640 			p_business_group_id		IN NUMBER	DEFAULT NULL,
641 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN HR_LOCATIONS_ALL%ROWTYPE
642 --------------------------------------------------------------------------------
643 	IS
644 		l_csr			RefCsr;
645 		l_rec			HR_LOCATIONS_ALL%ROWTYPE;
646 	BEGIN
647 		l_csr := csr_with_bus('HR_LOCATIONS_ALL','LOCATION_CODE','T',p_location_code,p_business_group_id);
648 		fetch l_csr into l_rec;
649 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
650 			l_rec := NULL;
651 		end if;
652 
653 		return l_rec;
654 	END;
655 --------------------------------------------------------------------------------
656 	FUNCTION location_id(
657 			p_location_code			IN VARCHAR2,
658 			p_business_group_id		IN NUMBER	DEFAULT NULL,
659 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
660 --------------------------------------------------------------------------------
661 	IS
662 	BEGIN
663 		return location_rec(p_location_code,p_business_group_id,p_error_when_not_exist).location_id;
664 	END;
665 --------------------------------------------------------------------------------
666 	FUNCTION organization_rec(
667 			p_name				IN VARCHAR2,
668 			p_business_group_id		IN NUMBER,
669 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN HR_ALL_ORGANIZATION_UNITS%ROWTYPE
670 --------------------------------------------------------------------------------
671 	IS
672 		l_csr			RefCsr;
673 		l_rec			HR_ALL_ORGANIZATION_UNITS%ROWTYPE;
674 	BEGIN
675 		l_csr := csr_with_bus('HR_ALL_ORGANIZATION_UNITS','NAME','T',p_name,p_business_group_id);
676 		fetch l_csr into l_rec;
677 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
678 			l_rec := NULL;
679 		end if;
680 
681 		return l_rec;
682 	END;
683 --------------------------------------------------------------------------------
684 	FUNCTION organization_id(
685 			p_name				IN VARCHAR2,
686 			p_business_group_id		IN NUMBER,
687 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
688 --------------------------------------------------------------------------------
689 	IS
690 	BEGIN
691 		return organization_rec(p_name,p_business_group_id,p_error_when_not_exist).organization_id;
692 	END;
693 --------------------------------------------------------------------------------
694 	FUNCTION org_payment_method_rec(
695 			p_org_payment_method_name	IN VARCHAR2,
696 			p_business_group_id		IN NUMBER,
697 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
698 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_ORG_PAYMENT_METHODS_F%ROWTYPE
699 --------------------------------------------------------------------------------
700 	IS
701 		l_csr			RefCsr;
702 		l_rec			PAY_ORG_PAYMENT_METHODS_F%ROWTYPE;
703 	BEGIN
704 		l_csr := csr_with_bus('PAY_ORG_PAYMENT_METHODS_F','ORG_PAYMENT_METHOD_NAME','T',p_org_payment_method_name,p_business_group_id,'DATETRACKED',p_effective_date);
705 		fetch l_csr into l_rec;
706 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
707 			l_rec := NULL;
708 		end if;
709 
710 		return l_rec;
711 	END;
712 --------------------------------------------------------------------------------
713 	FUNCTION org_payment_method_id(
714 			p_org_payment_method_name	IN VARCHAR2,
715 			p_business_group_id		IN NUMBER,
716 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
717 --------------------------------------------------------------------------------
718 	IS
719 	BEGIN
720 		return org_payment_method_rec(p_org_payment_method_name,p_business_group_id,NULL,p_error_when_not_exist).org_payment_method_id;
721 	END;
722 --------------------------------------------------------------------------------
723 	FUNCTION payroll_rec(
724 			p_payroll_name			IN VARCHAR2,
725 			p_business_group_id		IN NUMBER,
726 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
727 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_ALL_PAYROLLS_F%ROWTYPE
728 --------------------------------------------------------------------------------
729 	IS
730 		l_csr			RefCsr;
731 		l_rec			PAY_ALL_PAYROLLS_F%ROWTYPE;
732 	BEGIN
733 		l_csr := csr_with_bus('PAY_ALL_PAYROLLS_F','PAYROLL_NAME','T',p_payroll_name,p_business_group_id,'DATETRACKED',p_effective_date);
734 		fetch l_csr into l_rec;
735 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
736 			l_rec := NULL;
737 		end if;
738 
739 		return l_rec;
740 	END;
741 --------------------------------------------------------------------------------
742 	FUNCTION payroll_id(
743 			p_payroll_name			IN VARCHAR2,
744 			p_business_group_id		IN NUMBER,
745 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
746 --------------------------------------------------------------------------------
747 	IS
748 	BEGIN
749 		return payroll_rec(p_payroll_name,p_business_group_id,NULL,p_error_when_not_exist).payroll_id;
750 	END;
751 --------------------------------------------------------------------------------
752 	FUNCTION consolidation_set_rec(
753 			p_consolidation_set_name	IN VARCHAR2,
754 			p_business_group_id		IN NUMBER,
755 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_CONSOLIDATION_SETS%ROWTYPE
756 --------------------------------------------------------------------------------
757 	IS
758 		l_csr			RefCsr;
759 		l_rec			PAY_CONSOLIDATION_SETS%ROWTYPE;
760 	BEGIN
761 		l_csr := csr_with_bus('PAY_CONSOLIDATION_SETS','CONSOLIDATION_SET_NAME','T',p_consolidation_set_name,p_business_group_id);
762 		fetch l_csr into l_rec;
763 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
764 			l_rec := NULL;
765 		end if;
766 
767 		return l_rec;
768 	END;
769 --------------------------------------------------------------------------------
770 	FUNCTION consolidation_set_id(
771 			p_consolidation_set_name	IN VARCHAR2,
772 			p_business_group_id		IN NUMBER,
773 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
774 --------------------------------------------------------------------------------
775 	IS
776 	BEGIN
777 		return consolidation_set_rec(p_consolidation_set_name,p_business_group_id,p_error_when_not_exist).consolidation_set_id;
778 	END;
779 --------------------------------------------------------------------------------
780 	FUNCTION assignment_set_rec(
781 			p_assignment_set_name		IN VARCHAR2,
782 			p_business_group_id		IN NUMBER,
783 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN HR_ASSIGNMENT_SETS%ROWTYPE
784 --------------------------------------------------------------------------------
785 	IS
786 		l_csr			RefCsr;
787 		l_rec			HR_ASSIGNMENT_SETS%ROWTYPE;
788 	BEGIN
789 		l_csr := csr_with_bus('HR_ASSIGNMENT_SETS','ASSIGNMENT_SET_NAME','T',p_assignment_set_name,p_business_group_id);
790 		fetch l_csr into l_rec;
791 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
792 			l_rec := NULL;
793 		end if;
794 
795 		return l_rec;
796 	END;
797 --------------------------------------------------------------------------------
798 	FUNCTION assignment_set_id(
799 			p_assignment_set_name		IN VARCHAR2,
800 			p_business_group_id		IN NUMBER,
801 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
802 --------------------------------------------------------------------------------
803 	IS
804 	BEGIN
805 		return assignment_set_rec(p_assignment_set_name,p_business_group_id,p_error_when_not_exist).assignment_set_id;
806 	END;
807 --------------------------------------------------------------------------------
808 	FUNCTION pay_basis_rec(
809 			p_name				IN VARCHAR2,
810 			p_business_group_id		IN NUMBER,
811 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_PAY_BASES%ROWTYPE
812 --------------------------------------------------------------------------------
813 	IS
814 		l_csr			RefCsr;
815 		l_rec			PER_PAY_BASES%ROWTYPE;
816 	BEGIN
817 		l_csr := csr_with_bus('PER_PAY_BASES','NAME','T',p_name,p_business_group_id);
818 		fetch l_csr into l_rec;
819 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
820 			l_rec := NULL;
821 		end if;
822 
823 		return l_rec;
824 	END;
825 --------------------------------------------------------------------------------
826 	FUNCTION pay_basis_id(
827 			p_name				IN VARCHAR2,
828 			p_business_group_id		IN NUMBER,
829 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
830 --------------------------------------------------------------------------------
831 	IS
832 	BEGIN
833 		return pay_basis_rec(p_name,p_business_group_id,p_error_when_not_exist).pay_basis_id;
834 	END;
835 --------------------------------------------------------------------------------
836 	FUNCTION person_type_rec(
837 			p_user_person_type		IN VARCHAR2,
838 			p_business_group_id		IN NUMBER,
839 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_PERSON_TYPES%ROWTYPE
840 --------------------------------------------------------------------------------
841 	IS
842 		l_csr			RefCsr;
843 		l_rec			PER_PERSON_TYPES%ROWTYPE;
844 	BEGIN
845 		l_csr := csr_with_bus('PER_PERSON_TYPES','USER_PERSON_TYPE','T',p_user_person_type,p_business_group_id);
846 		fetch l_csr into l_rec;
847 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
848 			l_rec := NULL;
849 		end if;
850 
851 		return l_rec;
852 	END;
853 --------------------------------------------------------------------------------
854 	FUNCTION person_type_id(
855 			p_user_person_type		IN VARCHAR2,
856 			p_business_group_id		IN NUMBER,
857 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
858 --------------------------------------------------------------------------------
859 	IS
860 	BEGIN
861 		return person_type_rec(p_user_person_type,p_business_group_id,p_error_when_not_exist).person_type_id;
862 	END;
863 --------------------------------------------------------------------------------
864 	FUNCTION emp_person_rec(
865 			p_employee_number		IN VARCHAR2,
866 			p_business_group_id		IN NUMBER,
867 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
868 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_ALL_PEOPLE_F%ROWTYPE
869 --------------------------------------------------------------------------------
870 	IS
871 		l_csr			RefCsr;
872 		l_rec			PER_ALL_PEOPLE_F%ROWTYPE;
873 	BEGIN
874 		l_csr := csr_with_bus('PER_ALL_PEOPLE_F','EMPLOYEE_NUMBER','T',p_employee_number,p_business_group_id,'DATETRACKED',p_effective_date);
875 		fetch l_csr into l_rec;
876 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
877 			l_rec := NULL;
878 		end if;
879 
880 		return l_rec;
881 	END;
882 --------------------------------------------------------------------------------
883 	FUNCTION emp_person_id(
884 			p_employee_number		IN VARCHAR2,
885 			p_business_group_id		IN NUMBER,
886 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
887 --------------------------------------------------------------------------------
888 	IS
889 	BEGIN
890 		return emp_person_rec(p_employee_number,p_business_group_id,NULL,p_error_when_not_exist).person_id;
891 	END;
892 --------------------------------------------------------------------------------
893 	FUNCTION apl_person_rec(
894 			p_applicant_number		IN VARCHAR2,
895 			p_business_group_id		IN NUMBER,
896 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
897 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_ALL_PEOPLE_F%ROWTYPE
898 --------------------------------------------------------------------------------
899 	IS
900 		l_csr			RefCsr;
901 		l_rec			PER_ALL_PEOPLE_F%ROWTYPE;
902 	BEGIN
903 		l_csr := csr_with_bus('PER_ALL_PEOPLE_F','APPLICANT_NUMBER','T',p_applicant_number,p_business_group_id,'DATETRACKED',p_effective_date);
904 		fetch l_csr into l_rec;
905 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
906 			l_rec := NULL;
907 		end if;
908 
909 		return l_rec;
910 	END;
911 --------------------------------------------------------------------------------
912 	FUNCTION apl_person_id(
913 			p_applicant_number		IN VARCHAR2,
914 			p_business_group_id		IN NUMBER,
915 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
916 --------------------------------------------------------------------------------
917 	IS
918 	BEGIN
919 		return apl_person_rec(p_applicant_number,p_business_group_id,NULL,p_error_when_not_exist).person_id;
920 	END;
921 --------------------------------------------------------------------------------
922 	FUNCTION emp_assignment_rec(
923 			p_assignment_number		IN VARCHAR2,
924 			p_business_group_id		IN NUMBER,
925 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
926 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_ALL_ASSIGNMENTS_F%ROWTYPE
927 --------------------------------------------------------------------------------
928 	IS
929 		l_csr			RefCsr;
930 		l_rec			PER_ALL_ASSIGNMENTS_F%ROWTYPE;
931 	BEGIN
932 		l_csr := csr_with_bus('PER_ALL_ASSIGNMENTS_F','ASSIGNMENT_NUMBER','T',p_assignment_number,p_business_group_id,'DATETRACKED',p_effective_date);
933 		fetch l_csr into l_rec;
934 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
935 			l_rec := NULL;
936 		end if;
937 
938 		return l_rec;
939 	END;
940 --------------------------------------------------------------------------------
941 	FUNCTION emp_assignment_id(
942 			p_assignment_number		IN VARCHAR2,
943 			p_business_group_id		IN NUMBER,
944 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
945 --------------------------------------------------------------------------------
946 	IS
947 	BEGIN
948 		return emp_assignment_rec(p_assignment_number,p_business_group_id,NULL,p_error_when_not_exist).assignment_id;
949 	END;
950 --------------------------------------------------------------------------------
951 	FUNCTION rate_id(
952 			p_name				IN VARCHAR2,
953 			p_business_group_id		IN NUMBER,
954 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
955 --------------------------------------------------------------------------------
956 	IS
957 		l_csr			RefCsr;
958 		l_rec			PAY_RATES%ROWTYPE;
959 	BEGIN
960 		l_csr := csr_with_bus('PAY_RATES','NAME','T',p_name,p_business_group_id);
961 		fetch l_csr into l_rec;
962 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
963 			l_rec := NULL;
964 		end if;
965 
966 		return l_rec.rate_id;
967 	END;
968 --------------------------------------------------------------------------------
969 	FUNCTION job_rec(
970 			p_concatenated_segments		IN VARCHAR2,
971 			p_business_group_id		IN NUMBER,
972 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_JOBS%ROWTYPE
973 --------------------------------------------------------------------------------
974 	IS
975 		l_id_flex_num		NUMBER;
976 		l_job_definition_id	NUMBER;
977 		l_csr			RefCsr;
978 		l_rec			PER_JOBS%ROWTYPE;
979 	BEGIN
980 		l_id_flex_num := id_flex_num(p_business_group_id,'JOB');
981 		l_job_definition_id := keyflex_combination_id('PER','JOB',l_id_flex_num,p_concatenated_segments,p_error_when_not_exist);
982 
983 		if l_job_definition_id is NULL then
984 			l_rec := NULL;
985 		else
986 			l_csr := csr_with_bus('PER_JOBS','JOB_DEFINITION_ID','N',to_char(l_job_definition_id),p_business_group_id);
987 			fetch l_csr into l_rec;
988 			if not csr_found_and_close(l_csr,p_error_when_not_exist) then
989 				l_rec := NULL;
990 			end if;
991 		end if;
992 
993 		return l_rec;
994 	END;
995 --------------------------------------------------------------------------------
996 	FUNCTION job_id(
997 			p_concatenated_segments		IN VARCHAR2,
998 			p_business_group_id		IN NUMBER,
999 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1000 --------------------------------------------------------------------------------
1001 	IS
1002 	BEGIN
1003 		return job_rec(p_concatenated_segments,p_business_group_id,p_error_when_not_exist).job_id;
1004 	END;
1005 --------------------------------------------------------------------------------
1006 	FUNCTION position_rec(
1007 			p_concatenated_segments		IN VARCHAR2,
1008 			p_business_group_id		IN NUMBER,
1009 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_POSITIONS%ROWTYPE
1010 --------------------------------------------------------------------------------
1011 	IS
1012 		l_id_flex_num			NUMBER;
1013 		l_position_definition_id	NUMBER;
1014 		l_csr				RefCsr;
1015 		l_rec				PER_POSITIONS%ROWTYPE;
1016 	BEGIN
1017 		l_id_flex_num := id_flex_num(p_business_group_id,'POS');
1018 		l_position_definition_id := keyflex_combination_id('PER','POS',l_id_flex_num,p_concatenated_segments,p_error_when_not_exist);
1019 
1020 		if l_position_definition_id is NULL then
1021 			l_rec := NULL;
1022 		else
1023 			l_csr := csr_with_bus('PER_POSITIONS','POSITION_DEFINITION_ID','N',to_char(l_position_definition_id),p_business_group_id);
1024 			fetch l_csr into l_rec;
1025 			if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1026 				l_rec := NULL;
1027 			end if;
1028 		end if;
1029 
1030 		return l_rec;
1031 	END;
1032 --------------------------------------------------------------------------------
1033 	FUNCTION position_id(
1034 			p_concatenated_segments		IN VARCHAR2,
1035 			p_business_group_id		IN NUMBER,
1036 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1037 --------------------------------------------------------------------------------
1038 	IS
1039 	BEGIN
1040 		return position_rec(p_concatenated_segments,p_business_group_id,p_error_when_not_exist).position_id;
1041 	END;
1042 --------------------------------------------------------------------------------
1043 	FUNCTION grade_rec(
1044 			p_concatenated_segments		IN VARCHAR2,
1045 			p_business_group_id		IN NUMBER,
1046 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_GRADES%ROWTYPE
1047 --------------------------------------------------------------------------------
1048 	IS
1049 		l_id_flex_num		NUMBER;
1050 		l_grade_definition_id	NUMBER;
1051 		l_csr			RefCsr;
1052 		l_rec			PER_GRADES%ROWTYPE;
1053 	BEGIN
1054 		l_id_flex_num := id_flex_num(p_business_group_id,'GRD');
1055 		l_grade_definition_id := keyflex_combination_id('PER','GRD',l_id_flex_num,p_concatenated_segments,p_error_when_not_exist);
1056 
1057 		if l_grade_definition_id is NULL then
1058 			l_rec := NULL;
1059 		else
1060 			l_csr := csr_with_bus('PER_GRADES','GRADE_DEFINITION_ID','N',to_char(l_grade_definition_id),p_business_group_id);
1061 			fetch l_csr into l_rec;
1062 			if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1063 				l_rec := NULL;
1064 			end if;
1065 		end if;
1066 
1067 		return l_rec;
1068 	END;
1069 --------------------------------------------------------------------------------
1070 	FUNCTION grade_id(
1071 			p_concatenated_segments		IN VARCHAR2,
1072 			p_business_group_id		IN NUMBER,
1073 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1074 --------------------------------------------------------------------------------
1075 	IS
1076 	BEGIN
1077 		return grade_rec(p_concatenated_segments,p_business_group_id,p_error_when_not_exist).grade_id;
1078 	END;
1079 --------------------------------------------------------------------------------
1080 -- ID with special case
1081 --------------------------------------------------------------------------------
1082 	FUNCTION input_value_rec(
1083 			p_element_type_id		IN NUMBER,
1084 			p_name				IN VARCHAR2,
1085 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1086 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_INPUT_VALUES_F%ROWTYPE
1087 --------------------------------------------------------------------------------
1088 	IS
1089 		l_unique_column_tbl	UniColTbl;
1090 		l_csr			RefCsr;
1091 		l_rec			PAY_INPUT_VALUES_F%ROWTYPE;
1092 	BEGIN
1093 		unique_tbl_constructor('ELEMENT_TYPE_ID','N',to_char(p_element_type_id),l_unique_column_tbl);
1094 		unique_tbl_constructor('NAME','T',p_name,l_unique_column_tbl);
1095 
1096 		l_csr := csr('PAY_INPUT_VALUES_F',l_unique_column_tbl,'DATETRACKED',p_effective_date);
1097 		fetch l_csr into l_rec;
1098 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1099 			l_rec := NULL;
1100 		end if;
1101 
1102 		return l_rec;
1103 	END;
1104 --------------------------------------------------------------------------------
1105 	FUNCTION input_value_id(
1106 			p_element_type_id		IN NUMBER,
1107 			p_name				IN VARCHAR2,
1108 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1109 --------------------------------------------------------------------------------
1110 	IS
1111 	BEGIN
1112 		return input_value_rec(p_element_type_id,p_name,NULL,p_error_when_not_exist).input_value_id;
1113 	END;
1114 --------------------------------------------------------------------------------
1115 	FUNCTION input_value_rec(
1116 			p_element_name			IN VARCHAR2,
1117 			p_name				IN VARCHAR2,
1118 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1119 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1120 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1121 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_INPUT_VALUES_F%ROWTYPE
1122 --------------------------------------------------------------------------------
1123 	IS
1124 		l_element_type_id	NUMBER;
1125 	BEGIN
1126 		l_element_type_id := element_type_id(p_element_name,p_business_group_id,p_legislation_code,p_error_when_not_exist);
1127 
1128 		return input_value_rec(l_element_type_id,p_name,p_effective_date,p_error_when_not_exist);
1129 	END;
1130 --------------------------------------------------------------------------------
1131 	FUNCTION input_value_id(
1132 			p_element_name			IN VARCHAR2,
1133 			p_name				IN VARCHAR2,
1134 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1135 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1136 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1137 --------------------------------------------------------------------------------
1138 	IS
1139 	BEGIN
1140 		return input_value_rec(p_element_name,p_name,p_business_group_id,p_legislation_code,NULL,p_error_when_not_exist).input_value_id;
1141 	END;
1142 --------------------------------------------------------------------------------
1143 	FUNCTION defined_balance_rec(
1144 			p_balance_type_id		IN NUMBER,
1145 			p_balance_dimension_id		IN NUMBER,
1146 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1147 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1148 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_DEFINED_BALANCES%ROWTYPE
1149 --------------------------------------------------------------------------------
1150 	IS
1151 		l_unique_column_tbl	UniColTbl;
1152 		l_csr			RefCsr;
1153 		l_rec			PAY_DEFINED_BALANCES%ROWTYPE;
1154 	BEGIN
1155 		unique_tbl_constructor('BALANCE_TYPE_ID','N',to_char(p_balance_type_id),l_unique_column_tbl);
1156 		unique_tbl_constructor('BALANCE_DIMENSION_ID','N',to_char(p_balance_dimension_id),l_unique_column_tbl);
1157 		bus_leg_constructor(p_business_group_id,p_legislation_code,l_unique_column_tbl);
1158 
1159 		l_csr := csr('PAY_DEFINED_BALANCES',l_unique_column_tbl);
1160 		fetch l_csr into l_rec;
1161 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1162 			l_rec := NULL;
1163 		end if;
1164 
1165 		return l_rec;
1166 	END;
1167 --------------------------------------------------------------------------------
1168 	FUNCTION defined_balance_id(
1169 			p_balance_type_id		IN NUMBER,
1170 			p_balance_dimension_id		IN NUMBER,
1171 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1172 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1173 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1174 --------------------------------------------------------------------------------
1175 	IS
1176 	BEGIN
1177 		return defined_balance_rec(p_balance_type_id,p_balance_dimension_id,p_business_group_id,p_legislation_code,p_error_when_not_exist).defined_balance_id;
1178 	END;
1179 --------------------------------------------------------------------------------
1180 	FUNCTION defined_balance_rec(
1181 			p_balance_name			IN VARCHAR2,
1182 			p_dimension_name		IN VARCHAR2,
1183 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1184 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1185 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_DEFINED_BALANCES%ROWTYPE
1186 --------------------------------------------------------------------------------
1187 	IS
1188 		l_balance_type_id	NUMBER;
1189 		l_balance_dimension_id	NUMBER;
1190 	BEGIN
1191 		l_balance_type_id	:= balance_type_id(p_balance_name,p_business_group_id,p_legislation_code,p_error_when_not_exist);
1192 		l_balance_dimension_id	:= balance_dimension_id(p_dimension_name,p_business_group_id,p_legislation_code,p_error_when_not_exist);
1193 
1194 		return defined_balance_rec(l_balance_type_id,l_balance_dimension_id,p_business_group_id,p_legislation_code,p_error_when_not_exist);
1195 	END;
1196 --------------------------------------------------------------------------------
1197 	FUNCTION defined_balance_id(
1198 			p_balance_name			IN VARCHAR2,
1199 			p_dimension_name		IN VARCHAR2,
1200 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1201 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1202 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1203 --------------------------------------------------------------------------------
1204 	IS
1205 	BEGIN
1206 		return defined_balance_rec(p_balance_name,p_dimension_name,p_business_group_id,p_legislation_code,p_error_when_not_exist).defined_balance_id;
1207 	END;
1208 --------------------------------------------------------------------------------
1209 	FUNCTION balance_feed_rec(
1210 			p_balance_type_id		IN NUMBER,
1211 			p_input_value_id		IN NUMBER,
1212 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1213 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1214 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1215 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_BALANCE_FEEDS_F%ROWTYPE
1216 --------------------------------------------------------------------------------
1217 	IS
1218 		l_unique_column_tbl	UniColTbl;
1219 		l_csr			RefCsr;
1220 		l_rec			PAY_BALANCE_FEEDS_F%ROWTYPE;
1221 	BEGIN
1222 		unique_tbl_constructor('BALANCE_TYPE_ID','N',to_char(p_balance_type_id),l_unique_column_tbl);
1223 		unique_tbl_constructor('INPUT_VALUE_ID','N',to_char(p_input_value_id),l_unique_column_tbl);
1224 		bus_leg_constructor(p_business_group_id,p_legislation_code,l_unique_column_tbl);
1225 
1226 		l_csr := csr('PAY_BALANCE_FEEDS_F',l_unique_column_tbl,'DATETRACKED',p_effective_date);
1227 		fetch l_csr into l_rec;
1228 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1229 			l_rec := NULL;
1230 		end if;
1231 
1232 		return l_rec;
1233 	END;
1234 --------------------------------------------------------------------------------
1235 	FUNCTION balance_feed_id(
1236 			p_balance_type_id		IN NUMBER,
1237 			p_input_value_id		IN NUMBER,
1238 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1239 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1240 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1241 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1242 --------------------------------------------------------------------------------
1243 	IS
1244 	BEGIN
1245 		return balance_feed_rec(p_balance_type_id,p_input_value_id,p_business_group_id,p_legislation_code,p_effective_date,p_error_when_not_exist).balance_feed_id;
1246 	END;
1247 --------------------------------------------------------------------------------
1248 	FUNCTION balance_feed_rec(
1249 			p_balance_name			IN VARCHAR2,
1250 			p_element_name			IN VARCHAR2,
1251 			p_name				IN VARCHAR2,
1252 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1253 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1254 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1255 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_BALANCE_FEEDS_F%ROWTYPE
1256 --------------------------------------------------------------------------------
1257 	IS
1258 		l_balance_type_id	NUMBER;
1259 		l_input_value_id	NUMBER;
1260 	BEGIN
1261 		l_balance_type_id	:= balance_type_id(p_balance_name,p_business_group_id,p_legislation_code,p_error_when_not_exist);
1262 		l_input_value_id	:= input_value_id(p_element_name,p_name,p_business_group_id,p_legislation_code,p_error_when_not_exist);
1263 
1264 		return balance_feed_rec(l_balance_type_id,l_input_value_id,p_business_group_id,p_legislation_code,p_effective_date,p_error_when_not_exist);
1265 	END;
1266 --------------------------------------------------------------------------------
1267 	FUNCTION balance_feed_id(
1268 			p_balance_name			IN VARCHAR2,
1269 			p_element_name			IN VARCHAR2,
1270 			p_name				IN VARCHAR2,
1271 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1272 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1273 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1274 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1275 --------------------------------------------------------------------------------
1276 	IS
1277 	BEGIN
1278 		return balance_feed_rec(p_balance_name,p_element_name,p_name,p_business_group_id,p_legislation_code,p_effective_date,p_error_when_not_exist).balance_feed_id;
1279 	END;
1280 --------------------------------------------------------------------------------
1281 	FUNCTION business_group_rec(
1282 			p_name				IN VARCHAR2,
1283 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_BUSINESS_GROUPS%ROWTYPE
1284 --------------------------------------------------------------------------------
1285 	IS
1286 		l_unique_column_tbl	UniColTbl;
1287 		l_csr			RefCsr;
1288 		l_rec			PER_BUSINESS_GROUPS%ROWTYPE;
1289 	BEGIN
1290 		if g_bg_rec_cache.name = p_name then
1291 			NULL;
1292 		else
1293 			unique_tbl_constructor('NAME','T',p_name,l_unique_column_tbl);
1294 
1295 			l_csr := csr('PER_BUSINESS_GROUPS',l_unique_column_tbl);
1296 			fetch l_csr into l_rec;
1297 			if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1298 				l_rec := NULL;
1299 			end if;
1300 
1301 			g_bg_rec_cache := l_rec;
1302 		end if;
1303 
1304 		return g_bg_rec_cache;
1305 	END;
1306 --------------------------------------------------------------------------------
1307 	FUNCTION business_group_id(
1308 			p_name				IN VARCHAR2,
1309 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1310 --------------------------------------------------------------------------------
1311 	IS
1312 	BEGIN
1313 		return business_group_rec(p_name,p_error_when_not_exist).business_group_id;
1314 	END;
1315 --------------------------------------------------------------------------------
1316 	FUNCTION formula_type_rec(
1317 			p_formula_type_name		IN VARCHAR2,
1318 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN FF_FORMULA_TYPES%ROWTYPE
1319 --------------------------------------------------------------------------------
1320 	IS
1321 		l_unique_column_tbl	UniColTbl;
1322 		l_csr			RefCsr;
1323 		l_rec			FF_FORMULA_TYPES%ROWTYPE;
1324 	BEGIN
1325 		unique_tbl_constructor('FORMULA_TYPE_NAME','T',p_formula_type_name,l_unique_column_tbl);
1326 
1327 		l_csr := csr('FF_FORMULA_TYPES',l_unique_column_tbl);
1328 		fetch l_csr into l_rec;
1329 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1330 			l_rec := NULL;
1331 		end if;
1332 
1333 		return l_rec;
1334 	END;
1335 --------------------------------------------------------------------------------
1336 	FUNCTION formula_type_id(
1337 			p_formula_type_name		IN VARCHAR2,
1338 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1339 --------------------------------------------------------------------------------
1340 	IS
1341 	BEGIN
1342 		return formula_type_rec(p_formula_type_name,p_error_when_not_exist).formula_type_id;
1343 	END;
1344 --------------------------------------------------------------------------------
1345 	FUNCTION formula_rec(
1346 			p_formula_name			IN VARCHAR2,
1347 			p_formula_type_name		IN VARCHAR2,
1348 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1349 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1350 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1351 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN FF_FORMULAS_F%ROWTYPE
1352 --------------------------------------------------------------------------------
1353 	IS
1354 		l_formula_type_id	NUMBER;
1355 		l_unique_column_tbl	UniColTbl;
1356 		l_csr			RefCsr;
1357 		l_rec			FF_FORMULAS_F%ROWTYPE;
1358 	BEGIN
1359 		l_formula_type_id := formula_type_id(p_formula_type_name,p_error_when_not_exist);
1360 		unique_tbl_constructor('FORMULA_TYPE_ID','N',to_char(l_formula_type_id),l_unique_column_tbl);
1361 		unique_tbl_constructor('FORMULA_NAME','T',p_formula_name,l_unique_column_tbl);
1362 		bus_leg_constructor(p_business_group_id,p_legislation_code,l_unique_column_tbl);
1363 
1364 		l_csr := csr('FF_FORMULAS_F',l_unique_column_tbl,'DATETRACKED',p_effective_date);
1365 		fetch l_csr into l_rec;
1366 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1367 			l_rec := NULL;
1368 		end if;
1369 
1370 		return l_rec;
1371 	END;
1372 --------------------------------------------------------------------------------
1373 	FUNCTION formula_id(
1374 			p_formula_name			IN VARCHAR2,
1375 			p_formula_type_name		IN VARCHAR2,
1376 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1377 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1378 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1379 --------------------------------------------------------------------------------
1380 	IS
1381 	BEGIN
1382 		return formula_rec(p_formula_name,p_formula_type_name,p_business_group_id,p_legislation_code,NULL,p_error_when_not_exist).formula_id;
1383 	END;
1384 --------------------------------------------------------------------------------
1385 	FUNCTION function_rec(
1386 			p_name				IN VARCHAR2,
1387 			p_data_type			IN VARCHAR2,
1388 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1389 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1390 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN FF_FUNCTIONS%ROWTYPE
1391 --------------------------------------------------------------------------------
1392 	IS
1393 		l_unique_column_tbl	UniColTbl;
1394 		l_csr			RefCsr;
1395 		l_rec			FF_FUNCTIONS%ROWTYPE;
1396 	BEGIN
1397 		unique_tbl_constructor('NAME','T',p_name,l_unique_column_tbl);
1398 		unique_tbl_constructor('DATA_TYPE','T',p_data_type,l_unique_column_tbl);
1399 		bus_leg_constructor(p_business_group_id,p_legislation_code,l_unique_column_tbl);
1400 
1401 		l_csr := csr('FF_FUNCTIONS',l_unique_column_tbl);
1402 		fetch l_csr into l_rec;
1403 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1404 			l_rec := NULL;
1405 		end if;
1406 
1407 		return l_rec;
1408 	END;
1409 --------------------------------------------------------------------------------
1410 	FUNCTION function_id(
1411 			p_name				IN VARCHAR2,
1412 			p_data_type			IN VARCHAR2,
1413 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1414 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1415 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1416 --------------------------------------------------------------------------------
1417 	IS
1418 	BEGIN
1419 		return function_rec(p_name,p_data_type,p_business_group_id,p_legislation_code,p_error_when_not_exist).function_id;
1420 	END;
1421 --------------------------------------------------------------------------------
1422 	FUNCTION status_processing_rule_rec(
1423 			p_element_type_id		IN NUMBER,
1424 			p_assignment_status_type_id	IN NUMBER	DEFAULT NULL,
1425 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1426 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1427 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1428 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_STATUS_PROCESSING_RULES_F%ROWTYPE
1429 --------------------------------------------------------------------------------
1430 	IS
1431 		l_unique_column_tbl	UniColTbl;
1432 		l_csr			RefCsr;
1433 		l_rec			PAY_STATUS_PROCESSING_RULES_F%ROWTYPE;
1434 	BEGIN
1435 		unique_tbl_constructor('ELEMENT_TYPE_ID','N',to_char(p_element_type_id),l_unique_column_tbl);
1436 		unique_tbl_constructor('ASSIGNMENT_STATUS_TYPE_ID','N',to_char(p_assignment_status_type_id),l_unique_column_tbl);
1437 		bus_leg_constructor(p_business_group_id,p_legislation_code,l_unique_column_tbl);
1438 
1439 		l_csr := csr('PAY_STATUS_PROCESSING_RULES_F',l_unique_column_tbl,'DATETRACKED',p_effective_date);
1440 		fetch l_csr into l_rec;
1441 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1442 			l_rec := NULL;
1443 		end if;
1444 
1445 		return l_rec;
1446 	END;
1447 --------------------------------------------------------------------------------
1448 	FUNCTION status_processing_rule_id(
1449 			p_element_type_id		IN NUMBER,
1450 			p_assignment_status_type_id	IN NUMBER	DEFAULT NULL,
1451 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1452 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1453 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1454 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1455 --------------------------------------------------------------------------------
1456 	IS
1457 	BEGIN
1458 		return status_processing_rule_rec(
1459 				p_element_type_id,p_assignment_status_type_id,
1460 				p_business_group_id,p_legislation_code,p_effective_date,p_error_when_not_exist).status_processing_rule_id;
1461 	END;
1462 --------------------------------------------------------------------------------
1463 	FUNCTION formula_result_rule_rec(
1464 			p_status_processing_rule_id	IN NUMBER,
1465 			p_result_name			IN VARCHAR2,
1466 			p_result_rule_type		IN VARCHAR2,
1467 			p_element_type_id		IN NUMBER	DEFAULT NULL,
1468 			p_input_value_id		IN NUMBER	DEFAULT NULL,
1469 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1470 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1471 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1472 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_FORMULA_RESULT_RULES_F%ROWTYPE
1473 --------------------------------------------------------------------------------
1474 	IS
1475 		l_unique_column_tbl	UniColTbl;
1476 		l_csr			RefCsr;
1477 		l_rec			PAY_FORMULA_RESULT_RULES_F%ROWTYPE;
1478 	BEGIN
1479 		unique_tbl_constructor('STATUS_PROCESSING_RULE_ID','N',to_char(p_status_processing_rule_id),l_unique_column_tbl);
1480 		unique_tbl_constructor('RESULT_NAME','T',p_result_name,l_unique_column_tbl);
1481 		unique_tbl_constructor('RESULT_RULE_TYPE','T',p_result_rule_type,l_unique_column_tbl);
1482 		unique_tbl_constructor('ELEMENT_TYPE_ID + 0','N',to_char(p_element_type_id),l_unique_column_tbl);
1483 		unique_tbl_constructor('INPUT_VALUE_ID + 0','N',to_char(p_input_value_id),l_unique_column_tbl);
1484 		bus_leg_constructor(p_business_group_id,p_legislation_code,l_unique_column_tbl);
1485 
1486 		l_csr := csr('PAY_FORMULA_RESULT_RULES_F',l_unique_column_tbl,'DATETRACKED',p_effective_date);
1487 		fetch l_csr into l_rec;
1488 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1489 			l_rec := NULL;
1490 		end if;
1491 
1492 		return l_rec;
1493 	END;
1494 --------------------------------------------------------------------------------
1495 	FUNCTION formula_result_rule_id(
1496 			p_status_processing_rule_id	IN NUMBER,
1497 			p_result_name			IN VARCHAR2,
1498 			p_result_rule_type		IN VARCHAR2,
1499 			p_element_type_id		IN NUMBER	DEFAULT NULL,
1500 			p_input_value_id		IN NUMBER	DEFAULT NULL,
1501 			p_business_group_id		IN NUMBER	DEFAULT NULL,
1502 			p_legislation_code		IN VARCHAR2	DEFAULT NULL,
1503 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1504 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1505 --------------------------------------------------------------------------------
1506 	IS
1507 	BEGIN
1508 		return formula_result_rule_rec(
1509 				p_status_processing_rule_id,p_result_name,p_result_rule_type,p_element_type_id,p_input_value_id,
1510 				p_business_group_id,p_legislation_code,p_effective_date,p_error_when_not_exist).formula_result_rule_id;
1511 	END;
1512 --------------------------------------------------------------------------------
1513 	FUNCTION sub_classification_rule_rec(
1514 			p_element_type_id		IN NUMBER,
1515 			p_classification_id		IN NUMBER,
1516 			p_business_group_id		IN NUMBER,
1517 			p_legislation_code		IN VARCHAR2,
1518 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1519 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_SUB_CLASSIFICATION_RULES_F%ROWTYPE
1520 --------------------------------------------------------------------------------
1521 	IS
1522 		l_unique_column_tbl	UniColTbl;
1523 		l_csr			RefCsr;
1524 		l_rec			PAY_SUB_CLASSIFICATION_RULES_F%ROWTYPE;
1525 	BEGIN
1526 		unique_tbl_constructor('ELEMENT_TYPE_ID','N',to_char(p_element_type_id),l_unique_column_tbl);
1527 		unique_tbl_constructor('CLASSIFICATION_ID','N',to_char(p_classification_id),l_unique_column_tbl);
1528 		bus_leg_constructor(p_business_group_id,p_legislation_code,l_unique_column_tbl);
1529 
1530 		l_csr := csr('PAY_SUB_CLASSIFICATION_RULES_F',l_unique_column_tbl,'DATETRACKED',p_effective_date);
1531 		fetch l_csr into l_rec;
1532 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1533 			l_rec := NULL;
1534 		end if;
1535 
1536 		return l_rec;
1537 	END;
1538 --------------------------------------------------------------------------------
1539 	FUNCTION sub_classification_rule_id(
1540 			p_element_type_id		IN NUMBER,
1541 			p_classification_id		IN NUMBER,
1542 			p_business_group_id		IN NUMBER,
1543 			p_legislation_code		IN VARCHAR2,
1544 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1545 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1546 --------------------------------------------------------------------------------
1547 	IS
1548 	BEGIN
1549 		return sub_classification_rule_rec(
1550 				p_element_type_id,p_classification_id,p_business_group_id,p_legislation_code,
1551 				p_effective_date,p_error_when_not_exist).sub_classification_rule_id;
1552 	END;
1553 --------------------------------------------------------------------------------
1554 	FUNCTION element_link_id(
1555 			p_assignment_id			IN NUMBER,
1556 			p_element_type_id		IN NUMBER,
1557 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1558 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1559 --------------------------------------------------------------------------------
1560 	IS
1561 		l_element_link_id	NUMBER;
1562 	BEGIN
1563 		l_element_link_id := hr_entry_api.get_link(p_assignment_id,p_element_type_id,p_effective_date);
1564 
1565 		if p_error_when_not_exist = 'TRUE' and l_element_link_id is NULL then
1566 			fnd_message.set_name('PAY','HR_51271_ELE_NOT_ELIGIBLE');
1567 			fnd_message.raise_error;
1568 		end if;
1569 
1570 		return l_element_link_id;
1571 	END;
1572 --------------------------------------------------------------------------------
1573 	FUNCTION element_link_id(
1574 			p_assignment_number		IN VARCHAR2,
1575 			p_element_name			IN VARCHAR2,
1576 			p_business_group_id		IN NUMBER,
1577 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1578 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1579 --------------------------------------------------------------------------------
1580 	IS
1581 		l_assignment_id		NUMBER;
1582 		l_element_type_id	NUMBER;
1583 	BEGIN
1584 		l_assignment_id		:= emp_assignment_id(p_assignment_number,p_business_group_id,p_error_when_not_exist);
1585 		l_element_type_id	:= element_type_id(p_element_name,p_business_group_id,NULL,p_error_when_not_exist);
1586 
1587 		return element_link_id(l_assignment_id,l_element_type_id,p_effective_date,p_error_when_not_exist);
1588 	END;
1589 --------------------------------------------------------------------------------
1590 	FUNCTION org_information_rec(
1591 			p_organization_id		IN NUMBER,
1592 			p_org_information_context	IN VARCHAR2,
1593 			p_org_information1		IN VARCHAR2	DEFAULT NULL,
1594 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN HR_ORGANIZATION_INFORMATION%ROWTYPE
1595 --------------------------------------------------------------------------------
1596 	IS
1597 		l_unique_column_tbl	UniColTbl;
1598 		l_csr			RefCsr;
1599 		l_rec			HR_ORGANIZATION_INFORMATION%ROWTYPE;
1600 	BEGIN
1601 		unique_tbl_constructor('ORGANIZATION_ID','N',to_char(p_organization_id),l_unique_column_tbl);
1602 		unique_tbl_constructor('ORG_INFORMATION_CONTEXT','T',p_org_information_context,l_unique_column_tbl);
1603 		if p_org_information_context = 'CLASS' then
1604 			unique_tbl_constructor('ORG_INFORMATION1','T',p_org_information1,l_unique_column_tbl);
1605 		end if;
1606 
1607 		l_csr := csr('HR_ORGANIZATION_INFORMATION',l_unique_column_tbl);
1608 		fetch l_csr into l_rec;
1609 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1610 			l_rec := NULL;
1611 		end if;
1612 
1613 		return l_rec;
1614 	END;
1615 --------------------------------------------------------------------------------
1616 	FUNCTION org_information_id(
1617 			p_organization_id		IN NUMBER,
1618 			p_org_information_context	IN VARCHAR2,
1619 			p_org_information1		IN VARCHAR2	DEFAULT NULL,
1620 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1621 --------------------------------------------------------------------------------
1622 	IS
1623 	BEGIN
1624 		return org_information_rec(p_organization_id,p_org_information_context,p_org_information1,p_error_when_not_exist).org_information_id;
1625 	END;
1626 --------------------------------------------------------------------------------
1627 	FUNCTION org_information_rec(
1628 			p_name				IN VARCHAR2,
1629 			p_org_information_context	IN VARCHAR2,
1630 			p_org_information1		IN VARCHAR2	DEFAULT NULL,
1631 			p_business_group_id		IN VARCHAR2,
1632 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN HR_ORGANIZATION_INFORMATION%ROWTYPE
1633 --------------------------------------------------------------------------------
1634 	IS
1635 		l_organization_id	NUMBER;
1636 	BEGIN
1637 		l_organization_id := organization_id(p_name,p_business_group_id);
1638 
1639 		return org_information_rec(l_organization_id,p_org_information_context,p_org_information1,p_error_when_not_exist);
1640 	END;
1641 --------------------------------------------------------------------------------
1642 	FUNCTION org_information_id(
1643 			p_name				IN VARCHAR2,
1644 			p_org_information_context	IN VARCHAR2,
1645 			p_org_information1		IN VARCHAR2	DEFAULT NULL,
1646 			p_business_group_id		IN VARCHAR2,
1647 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1648 --------------------------------------------------------------------------------
1649 	IS
1650 	BEGIN
1651 		return org_information_rec(p_name,p_org_information_context,p_org_information1,p_business_group_id,p_error_when_not_exist).org_information_id;
1652 	END;
1653 --------------------------------------------------------------------------------
1654 	FUNCTION payment_defined_balance_rec(
1655 			p_business_group_id		IN NUMBER,
1656 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_DEFINED_BALANCES%ROWTYPE
1657 --------------------------------------------------------------------------------
1658 	IS
1659 		l_rec			PAY_DEFINED_BALANCES%ROWTYPE;
1660 		l_legislation_code	VARCHAR2(2);
1661 		CURSOR csr_payment_defined_balance_id IS
1662 			select	pdb.*
1663 			from	pay_balance_dimensions	pbd,
1664 				pay_defined_balances	pdb,
1665 				pay_balance_types	pbt
1666 			where	pbt.assignment_remuneration_flag='Y'
1667 			and	nvl(pbt.business_group_id,p_business_group_id)=p_business_group_id
1668 			and	nvl(pbt.legislation_code,l_legislation_code)=l_legislation_code
1669 			and	pdb.balance_type_id=pbt.balance_type_id
1670 			and	nvl(pdb.business_group_id,p_business_group_id)=p_business_group_id
1671 			and	nvl(pdb.legislation_code,l_legislation_code)=l_legislation_code
1672 			and	pbd.balance_dimension_id=pdb.balance_dimension_id
1673 			and	nvl(pbd.business_group_id,p_business_group_id)=p_business_group_id
1674 			and	nvl(pbd.legislation_code,l_legislation_code)=l_legislation_code
1675 			and	pbd.payments_flag='Y';
1676 	BEGIN
1677 		l_legislation_code :=  legislation_code(p_business_group_id);
1678 
1679 		open csr_payment_defined_balance_id;
1680 		fetch csr_payment_defined_balance_id into l_rec;
1681 		if csr_payment_defined_balance_id%NOTFOUND then
1682 			if p_error_when_not_exist = 'TRUE' then
1683 				close csr_payment_defined_balance_id;
1684 
1685 				fnd_message.set_name('PER','HR_JP_ID_NOT_FOUND');
1686 				fnd_message.raise_error;
1687 			else
1688 				l_rec := NULL;
1689 			end if;
1690 		end if;
1691 		close csr_payment_defined_balance_id;
1692 
1693 		return l_rec;
1694 	END;
1695 --------------------------------------------------------------------------------
1696 	FUNCTION payment_defined_balance_id(
1697 			p_business_group_id		IN NUMBER,
1698 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1699 --------------------------------------------------------------------------------
1700 	IS
1701 	BEGIN
1702 		return payment_defined_balance_rec(p_business_group_id,p_error_when_not_exist).defined_balance_id;
1703 	END;
1704 --------------------------------------------------------------------------------
1705 	FUNCTION legislation_rule_mode(
1706 			p_legislation_code		IN VARCHAR2,
1707 			p_rule_type			IN VARCHAR2,
1708 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN VARCHAR2
1709 --------------------------------------------------------------------------------
1710 	IS
1711 		l_unique_column_tbl	UniColTbl;
1712 		l_csr			RefCsr;
1713 		l_rec			PAY_LEGISLATION_RULES%ROWTYPE;
1714 	BEGIN
1715 		unique_tbl_constructor('LEGISLATION_CODE','T',p_legislation_code,l_unique_column_tbl);
1716 		unique_tbl_constructor('RULE_TYPE','T',p_rule_type,l_unique_column_tbl);
1717 
1718 		l_csr := csr('PAY_LEGISLATION_RULES',l_unique_column_tbl);
1719 		fetch l_csr into l_rec;
1720 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1721 			l_rec := NULL;
1722 		end if;
1723 
1724 		return l_rec.rule_mode;
1725 	END;
1726 --------------------------------------------------------------------------------
1727 	FUNCTION payment_type_rec(
1728 			p_payment_type_name		IN VARCHAR2,
1729 			p_territory_code		IN VARCHAR2	DEFAULT NULL,
1730 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_PAYMENT_TYPES%ROWTYPE
1731 --------------------------------------------------------------------------------
1732 	IS
1733 		l_unique_column_tbl	UniColTbl;
1734 		l_csr			RefCsr;
1735 		l_rec			PAY_PAYMENT_TYPES%ROWTYPE;
1736 	BEGIN
1737 		unique_tbl_constructor('PAYMENT_TYPE_NAME','T',p_payment_type_name,l_unique_column_tbl);
1738 		if p_territory_code = C_DEFAULT_LEG then
1739 			NULL;
1740 		else
1741 			unique_tbl_constructor('nvl(TERRITORY_CODE,''' || p_territory_code || ''')','T',p_territory_code,l_unique_column_tbl);
1742 		end if;
1743 
1744 		l_csr := csr('PAY_PAYMENT_TYPES',l_unique_column_tbl);
1745 		fetch l_csr into l_rec;
1746 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1747 			l_rec := NULL;
1748 		end if;
1749 
1750 		return l_rec;
1751 	END;
1752 --------------------------------------------------------------------------------
1753 	FUNCTION payment_type_id(
1754 			p_payment_type_name		IN VARCHAR2,
1755 			p_territory_code		IN VARCHAR2	DEFAULT NULL,
1756 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN VARCHAR2
1757 --------------------------------------------------------------------------------
1758 	IS
1759 	BEGIN
1760 		return payment_type_rec(p_payment_type_name,p_territory_code,p_error_when_not_exist).payment_type_id;
1761 	END;
1762 --------------------------------------------------------------------------------
1763 	FUNCTION grade_rule_rec(
1764 			p_rate_id			IN NUMBER,
1765 			p_grade_id			IN NUMBER,
1766 --			p_rate_type			IN VARCHAR2,
1767 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_GRADE_RULES_F%ROWTYPE
1768 --------------------------------------------------------------------------------
1769 	IS
1770 		l_unique_column_tbl	UniColTbl;
1771 		l_csr			RefCsr;
1772 		l_rec			PAY_GRADE_RULES_F%ROWTYPE;
1773 	BEGIN
1774 		unique_tbl_constructor('RATE_ID','N',to_char(p_rate_id),l_unique_column_tbl);
1775 		unique_tbl_constructor('GRADE_OR_SPINAL_POINT_ID','N',to_char(p_grade_id),l_unique_column_tbl);
1776 		-- Current version supports only 'GRADE'. 'SPINAL POINT' is not supported.
1777 		unique_tbl_constructor('RATE_TYPE','T','G',l_unique_column_tbl);
1778 
1779 		l_csr := csr('PAY_GRADE_RULES_F',l_unique_column_tbl);
1780 		fetch l_csr into l_rec;
1781 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1782 			l_rec := NULL;
1783 		end if;
1784 
1785 		return l_rec;
1786 	END;
1787 --------------------------------------------------------------------------------
1788 	FUNCTION grade_rule_id(
1789 			p_rate_id			IN NUMBER,
1790 			p_grade_id			IN NUMBER,
1791 --			p_rate_type			IN VARCHAR2,
1792 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1793 --------------------------------------------------------------------------------
1794 	IS
1795 	BEGIN
1796 		return grade_rule_rec(p_rate_id,p_grade_id,p_error_when_not_exist).grade_rule_id;
1797 	END;
1798 --------------------------------------------------------------------------------
1799 	FUNCTION flex_value_set_rec(
1800 			p_flex_value_set_name		IN VARCHAR2,
1801 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN FND_FLEX_VALUE_SETS%ROWTYPE
1802 --------------------------------------------------------------------------------
1803 	IS
1804 		l_unique_column_tbl	UniColTbl;
1805 		l_csr			RefCsr;
1806 		l_rec			FND_FLEX_VALUE_SETS%ROWTYPE;
1807 	BEGIN
1808 		unique_tbl_constructor('FLEX_VALUE_SET_NAME','T',p_flex_value_set_name,l_unique_column_tbl);
1809 
1810 		l_csr := csr('FND_FLEX_VALUE_SETS',l_unique_column_tbl);
1811 		fetch l_csr into l_rec;
1812 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1813 			l_rec := NULL;
1814 		end if;
1815 
1816 		return l_rec;
1817 	END;
1818 --------------------------------------------------------------------------------
1819 	FUNCTION flex_value_set_id(
1820 			p_flex_value_set_name		IN VARCHAR2,
1821 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1822 --------------------------------------------------------------------------------
1823 	IS
1824 	BEGIN
1825 		return flex_value_set_rec(p_flex_value_set_name,p_error_when_not_exist).flex_value_set_id;
1826 	END;
1827 --------------------------------------------------------------------------------
1828 	FUNCTION flex_value_rec(
1829 			p_flex_value_set_id		IN NUMBER,
1830 			p_flex_value			IN VARCHAR2,
1831 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN FND_FLEX_VALUES%ROWTYPE
1832 --------------------------------------------------------------------------------
1833 	IS
1834 		l_unique_column_tbl	UniColTbl;
1835 		l_csr			RefCsr;
1836 		l_rec			FND_FLEX_VALUES%ROWTYPE;
1837 	BEGIN
1838 		unique_tbl_constructor('FLEX_VALUE_SET_ID','N',to_char(p_flex_value_set_id),l_unique_column_tbl);
1839 		unique_tbl_constructor('FLEX_VALUE','T',p_flex_value,l_unique_column_tbl);
1840 
1841 		l_csr := csr('FND_FLEX_VALUES',l_unique_column_tbl);
1842 		fetch l_csr into l_rec;
1843 		if not csr_found_and_close(l_csr,p_error_when_not_exist) then
1844 			l_rec := NULL;
1845 		end if;
1846 
1847 		return l_rec;
1848 	END;
1849 --------------------------------------------------------------------------------
1850 	FUNCTION flex_value_id(
1851 			p_flex_value_set_id		IN NUMBER,
1852 			p_flex_value			IN VARCHAR2,
1853 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
1854 --------------------------------------------------------------------------------
1855 	IS
1856 	BEGIN
1857 		return flex_value_rec(p_flex_value_set_id,p_flex_value,p_error_when_not_exist).flex_value_id;
1858 	END;
1859 --------------------------------------------------------------------------------
1860 	FUNCTION period_of_service_rec(
1861 			p_employee_number		IN VARCHAR2,
1862 			p_business_group_id		IN NUMBER,
1863 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1864 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_PERIODS_OF_SERVICE%ROWTYPE
1865 --------------------------------------------------------------------------------
1866 	IS
1867 		l_rec	PER_PERIODS_OF_SERVICE%ROWTYPE;
1868 		CURSOR csr_period_of_service IS
1869 			select	pps.*
1870 			from	per_periods_of_service	pps,
1871 				per_all_people_f	pp
1872 			where	pp.employee_number=p_employee_number
1873 			and	pp.business_group_id=p_business_group_id
1874 			and	p_effective_date
1875 				between pp.effective_start_date and pp.effective_end_date
1876 			and	pps.person_id=pp.person_id
1877 			and	(	pps.date_start=(
1878 						select	max(pps2.date_start)
1879 						from	per_periods_of_service	pps2
1880 						where	pps2.person_id=pp.person_id
1881 						and	pps2.date_start <= pp.effective_end_date)
1882 				);
1883 	BEGIN
1884 		open csr_period_of_service;
1885 		fetch csr_period_of_service into l_rec;
1886 		if csr_period_of_service%NOTFOUND then
1887 			if p_error_when_not_exist = 'TRUE' then
1888 				close csr_period_of_service;
1889 
1890 				fnd_message.set_name('PER','HR_JP_PERSON_NOT_FOUND');
1891 				fnd_message.set_token('EMPLOYEE_NUMBER',p_employee_number);
1892 				fnd_message.raise_error;
1893 			end if;
1894 		end if;
1895 		close csr_period_of_service;
1896 
1897 		return l_rec;
1898 	END;
1899 --------------------------------------------------------------------------------
1900 	FUNCTION element_entry_rec(
1901 			p_assignment_id			IN VARCHAR2,
1902 			p_element_type_id		IN VARCHAR2,
1903 			p_entry_type			IN VARCHAR2	DEFAULT 'E',
1904 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1905 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_ELEMENT_ENTRIES_F%ROWTYPE
1906 --------------------------------------------------------------------------------
1907 	IS
1908 		l_rec			PAY_ELEMENT_ENTRIES_F%ROWTYPE;
1909 		CURSOR csr_element_entry(
1910 					p_assignment_id		NUMBER,
1911 					p_element_type_id	NUMBER) IS
1912 			select	pee.*
1913 			from	pay_element_links_f	pel,
1914 				pay_element_entries_f	pee
1915 			where	pee.assignment_id=p_assignment_id
1916 			and	pee.entry_type=p_entry_type
1917 			and	p_effective_date
1918 				between pee.effective_start_date and pee.effective_end_date
1919 			and	pel.element_link_id=pee.element_link_id
1920 			and	pel.element_type_id=p_element_type_id
1921 			and	p_effective_date
1922 				between pel.effective_start_date and pel.effective_end_date
1923 			and	pee.entry_type='E'
1924 			order by pee.element_entry_id;
1925 	BEGIN
1926 		open csr_element_entry(p_assignment_id,p_element_type_id);
1927 		fetch csr_element_entry into l_rec;
1928 		if csr_element_entry%NOTFOUND then
1929 			if p_error_when_not_exist = 'TRUE' then
1930 				close csr_element_entry;
1931 
1932 				fnd_message.set_name('PER','HR_JP_ELEMENT_ENTRY_NOT_FOUND');
1933 				fnd_message.raise_error;
1934 			else
1935 				l_rec := NULL;
1936 			end if;
1937 		end if;
1938 		close csr_element_entry;
1939 
1940 		return l_rec;
1941 	END;
1942 --------------------------------------------------------------------------------
1943 	FUNCTION element_entry_rec(
1944 			p_assignment_number		IN VARCHAR2,
1945 			p_element_name			IN VARCHAR2,
1946 			p_entry_type			IN VARCHAR2	DEFAULT 'E',
1947 			p_business_group_id		IN NUMBER,
1948 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1949 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_ELEMENT_ENTRIES_F%ROWTYPE
1950 --------------------------------------------------------------------------------
1951 	IS
1952 		l_assignment_id		NUMBER := emp_assignment_id(p_assignment_number,p_business_group_id);
1953 		l_element_type_id	NUMBER := element_type_id(p_element_name,p_business_group_id);
1954 	BEGIN
1955 		return element_entry_rec(l_assignment_id,l_element_type_id,p_entry_type,p_business_group_id,p_effective_date,p_error_when_not_exist);
1956 	END;
1957 --------------------------------------------------------------------------------
1958 	FUNCTION address_rec(
1959 		-- primary_flag has higher priority then address_type.
1960 		-- If you pass both p_primary_flag = 'N' and p_address_type is NULL,
1961 		-- record this function returns is inexact.
1962 			p_person_id			IN NUMBER,
1963 			p_primary_flag			IN VARCHAR2	DEFAULT 'Y',
1964 			p_address_type			IN VARCHAR2	DEFAULT NULL,
1965 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
1966 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_ADDRESSES%ROWTYPE
1967 --------------------------------------------------------------------------------
1968 	IS
1969 		l_unique_column_tbl	UniColTbl;
1970 		l_csr			RefCsr;
1971 		l_rec			PER_ADDRESSES%ROWTYPE;
1972 	BEGIN
1973 		unique_tbl_constructor('PERSON_ID','N',to_char(p_person_id),l_unique_column_tbl);
1974 		if p_primary_flag = 'Y' then
1975 			unique_tbl_constructor('PRIMARY_FLAG','T',p_primary_flag,l_unique_column_tbl);
1976 		else
1977 			unique_tbl_constructor('ADDRESS_TYPE','T',p_address_type,l_unique_column_tbl);
1978 		end if;
1979 		l_csr := csr('PER_ADDRESSES',l_unique_column_tbl,'DATED',p_effective_date);
1980 		fetch l_csr into l_rec;
1981 		if l_csr%NOTFOUND then
1982 			if p_error_when_not_exist = 'TRUE' then
1983 				close l_csr;
1984 				fnd_message.set_name('PER','HR_JP_ID_NOT_FOUND');
1985 				fnd_message.set_token('SQL',g_sql);
1986 				fnd_message.raise_error;
1987 			else
1988 				l_rec := NULL;
1989 			end if;
1990 		end if;
1991 		close l_csr;
1992 
1993 		return l_rec;
1994 	END;
1995 --------------------------------------------------------------------------------
1996 	FUNCTION primary_address_rec(
1997 		-- This function is valid when address_type is not NULL.
1998 			p_person_id			IN NUMBER,
1999 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
2000 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_ADDRESSES%ROWTYPE
2001 --------------------------------------------------------------------------------
2002 	IS
2003 	BEGIN
2004 		return address_rec(p_person_id,'Y',NULL,p_effective_date,p_error_when_not_exist);
2005 	END;
2006 --------------------------------------------------------------------------------
2007 	FUNCTION address_rec(
2008 		-- This function is valid when address_type is not NULL.
2009 			p_person_id			IN NUMBER,
2010 			p_address_type			IN VARCHAR2,
2011 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
2012 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_ADDRESSES%ROWTYPE
2013 --------------------------------------------------------------------------------
2014 	IS
2015 	BEGIN
2016 		return address_rec(p_person_id,'N',p_address_type,p_effective_date,p_error_when_not_exist);
2017 	END;
2018 --------------------------------------------------------------------------------
2019 	FUNCTION emp_primary_address_rec(
2020 		-- This function is valid when address_type is not NULL.
2021 			p_employee_number		IN VARCHAR2,
2022 			p_business_group_id		IN NUMBER,
2023 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
2024 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_ADDRESSES%ROWTYPE
2025 --------------------------------------------------------------------------------
2026 	IS
2027 		l_person_id	NUMBER;
2028 	BEGIN
2029 		l_person_id := emp_person_id(p_employee_number,p_business_group_id);
2030 
2031 		return primary_address_rec(l_person_id,p_effective_date,p_error_when_not_exist);
2032 	END;
2033 --------------------------------------------------------------------------------
2034 	FUNCTION emp_address_rec(
2035 		-- This function is valid when address_type is not NULL.
2036 			p_employee_number		IN VARCHAR2,
2037 			p_address_type			IN VARCHAR2,
2038 			p_business_group_id		IN NUMBER,
2039 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
2040 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_ADDRESSES%ROWTYPE
2041 --------------------------------------------------------------------------------
2042 	IS
2043 		l_person_id	NUMBER;
2044 	BEGIN
2045 		l_person_id := emp_person_id(p_employee_number,p_business_group_id);
2046 
2047 		return address_rec(l_person_id,p_address_type,p_effective_date,p_error_when_not_exist);
2048 	END;
2049 --------------------------------------------------------------------------------
2050 	FUNCTION apl_primary_address_rec(
2051 		-- This function is valid when address_type is not NULL.
2052 			p_applicant_number		IN VARCHAR2,
2053 			p_business_group_id		IN NUMBER,
2054 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
2055 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_ADDRESSES%ROWTYPE
2056 --------------------------------------------------------------------------------
2057 	IS
2058 		l_person_id	NUMBER;
2059 	BEGIN
2060 		l_person_id := apl_person_id(p_applicant_number,p_business_group_id);
2061 
2062 		return primary_address_rec(l_person_id,p_effective_date,p_error_when_not_exist);
2063 	END;
2064 --------------------------------------------------------------------------------
2065 	FUNCTION apl_address_rec(
2066 		-- This function is valid when address_type is not NULL.
2067 			p_applicant_number		IN VARCHAR2,
2068 			p_address_type			IN VARCHAR2,
2069 			p_business_group_id		IN NUMBER,
2070 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
2071 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_ADDRESSES%ROWTYPE
2072 --------------------------------------------------------------------------------
2073 	IS
2074 		l_person_id	NUMBER;
2075 	BEGIN
2076 		l_person_id := apl_person_id(p_applicant_number,p_business_group_id);
2077 
2078 		return address_rec(l_person_id,p_address_type,p_effective_date,p_error_when_not_exist);
2079 	END;
2080 --------------------------------------------------------------------------------
2081 	FUNCTION personal_payment_method_rec(
2082 			p_assignment_id			IN NUMBER,
2083 			p_priority			IN NUMBER,
2084 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
2085 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_PERSONAL_PAYMENT_METHODS_F%ROWTYPE
2086 --------------------------------------------------------------------------------
2087 	IS
2088 		l_unique_column_tbl	UniColTbl;
2089 		l_csr			RefCsr;
2090 		l_rec			PAY_PERSONAL_PAYMENT_METHODS_F%ROWTYPE;
2091 	BEGIN
2092 		unique_tbl_constructor('ASSIGNMENT_ID','N',to_char(p_assignment_id),l_unique_column_tbl);
2093 		unique_tbl_constructor('PRIORITY','N',to_char(p_priority),l_unique_column_tbl);
2094 		l_csr := csr('PAY_PERSONAL_PAYMENT_METHODS_F',l_unique_column_tbl,'DATETRACKED',p_effective_date);
2095 		fetch l_csr into l_rec;
2096 		if l_csr%NOTFOUND then
2097 			if p_error_when_not_exist = 'TRUE' then
2098 				close l_csr;
2099 				fnd_message.set_name('PER','HR_JP_ID_NOT_FOUND');
2100 				fnd_message.set_token('SQL',g_sql);
2101 				fnd_message.raise_error;
2102 			else
2103 				l_rec := NULL;
2104 			end if;
2105 		end if;
2106 		close l_csr;
2107 
2108 		return l_rec;
2109 	END;
2110 --------------------------------------------------------------------------------
2111 	FUNCTION personal_payment_method_rec(
2112 			p_assignment_number		IN VARCHAR2,
2113 			p_priority			IN NUMBER,
2114 			p_business_group_id		IN NUMBER,
2115 			p_effective_date		IN DATE		DEFAULT hr_api.g_sys,
2116 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_PERSONAL_PAYMENT_METHODS_F%ROWTYPE
2117 --------------------------------------------------------------------------------
2118 	IS
2119 		l_assignment_id		NUMBER;
2120 	BEGIN
2121 		l_assignment_id := emp_assignment_id(p_assignment_number,p_business_group_id);
2122 
2123 		return personal_payment_method_rec(l_assignment_id,p_priority,p_effective_date,p_error_when_not_exist);
2124 	END;
2125 --------------------------------------------------------------------------------
2126 	FUNCTION JP_BANK_REC(
2127 			P_BANK_CODE			IN VARCHAR2,
2128 			P_BRANCH_CODE			IN VARCHAR2,
2129 			p_error_when_not_exist		IN VARCHAR2	DEFAULT 'TRUE') RETURN PER_JP_BANK_LOOKUPS%ROWTYPE
2130 --------------------------------------------------------------------------------
2131 	IS
2132 		l_unique_column_tbl	UniColTbl;
2133 		l_csr			RefCsr;
2134 		l_rec			PER_JP_BANK_LOOKUPS%ROWTYPE;
2135 	BEGIN
2136 		unique_tbl_constructor('BANK_CODE','T',p_bank_code,l_unique_column_tbl);
2137 		unique_tbl_constructor('BRANCH_CODE','T',p_branch_code,l_unique_column_tbl);
2138 		l_csr := csr('PER_JP_BANK_LOOKUPS',l_unique_column_tbl);
2139 		fetch l_csr into l_rec;
2140 		if l_csr%NOTFOUND then
2141 			if p_error_when_not_exist = 'TRUE' then
2142 				close l_csr;
2143 				fnd_message.set_name('PER','HR_JP_ID_NOT_FOUND');
2144 				fnd_message.set_token('SQL',g_sql);
2145 				fnd_message.raise_error;
2146 			else
2147 				l_rec := NULL;
2148 			end if;
2149 		end if;
2150 		close l_csr;
2151 
2152 		return l_rec;
2153 	END;
2154 --------------------------------------------------------------------------------
2155 	FUNCTION element_link_rec(
2156 			P_ELEMENT_TYPE_ID		IN NUMBER,
2157 			P_ORGANIZATION_ID		IN NUMBER	DEFAULT NULL,
2158 			P_PEOPLE_GROUP_ID		IN NUMBER	DEFAULT NULL,
2159 			P_JOB_ID			IN NUMBER	DEFAULT NULL,
2160 			P_POSITION_ID			IN NUMBER	DEFAULT NULL,
2161 			P_GRADE_ID			IN NUMBER	DEFAULT NULL,
2162 			P_LOCATION_ID			IN NUMBER	DEFAULT NULL,
2163 			P_EMPLOYMENT_CATEGORY		IN VARCHAR2	DEFAULT NULL,
2164 			P_PAYROLL_ID			IN NUMBER	DEFAULT NULL,
2165 			P_LINK_TO_ALL_PAYROLLS_FLAG	IN VARCHAR2	DEFAULT 'N',
2166 			P_PAY_BASIS_ID			IN NUMBER	DEFAULT NULL,
2167 			P_BUSINESS_GROUP_ID		IN NUMBER,
2168 			P_EFFECTIVE_DATE		IN DATE		DEFAULT hr_api.g_sys,
2169 			P_ERROR_WHEN_NOT_EXIST		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_ELEMENT_LINKS_F%ROWTYPE
2170 --------------------------------------------------------------------------------
2171 	IS
2172 		l_unique_column_tbl	UniColTbl;
2173 		l_csr			RefCsr;
2174 		l_rec			PAY_ELEMENT_LINKS_F%ROWTYPE;
2175 	BEGIN
2176 		unique_tbl_constructor('ELEMENT_TYPE_ID','N',to_char(p_element_type_id),l_unique_column_tbl);
2177 		unique_tbl_constructor('ORGANIZATION_ID','N',to_char(p_organization_id),l_unique_column_tbl);
2178 		unique_tbl_constructor('PEOPLE_GROUP_ID','N',to_char(p_people_group_id),l_unique_column_tbl);
2179 		unique_tbl_constructor('JOB_ID','N',to_char(p_job_id),l_unique_column_tbl);
2180 		unique_tbl_constructor('POSITION_ID','N',to_char(p_position_id),l_unique_column_tbl);
2181 		unique_tbl_constructor('GRADE_ID','N',to_char(p_grade_id),l_unique_column_tbl);
2182 		unique_tbl_constructor('LOCATION_ID','N',to_char(p_location_id),l_unique_column_tbl);
2183 		unique_tbl_constructor('EMPLOYMENT_CATEGORY','T',p_employment_category,l_unique_column_tbl);
2184 		unique_tbl_constructor('PAYROLL_ID','N',to_char(p_payroll_id),l_unique_column_tbl);
2185 		unique_tbl_constructor('LINK_TO_ALL_PAYROLLS_FLAG','T',p_link_to_all_payrolls_flag,l_unique_column_tbl);
2186 		unique_tbl_constructor('PAY_BASIS_ID','N',to_char(p_pay_basis_id),l_unique_column_tbl);
2187 		unique_tbl_constructor('BUSINESS_GROUP_ID + 0','N',to_char(p_business_group_id),l_unique_column_tbl);
2188 		l_csr := csr('PAY_ELEMENT_LINKS_F',l_unique_column_tbl,'DATETRACKED',p_effective_date);
2189 		fetch l_csr into l_rec;
2190 		if l_csr%NOTFOUND then
2191 			if p_error_when_not_exist = 'TRUE' then
2192 				close l_csr;
2193 				fnd_message.set_name('PER','HR_JP_ID_NOT_FOUND');
2194 				fnd_message.set_token('SQL',g_sql);
2195 				fnd_message.raise_error;
2196 			else
2197 				l_rec := NULL;
2198 			end if;
2199 		end if;
2200 		close l_csr;
2201 
2202 		return l_rec;
2203 	END;
2204 --------------------------------------------------------------------------------
2205 	FUNCTION element_link_id(
2206 			P_ELEMENT_TYPE_ID		IN NUMBER,
2207 			P_ORGANIZATION_ID		IN NUMBER	DEFAULT NULL,
2208 			P_PEOPLE_GROUP_ID		IN NUMBER	DEFAULT NULL,
2209 			P_JOB_ID			IN NUMBER	DEFAULT NULL,
2210 			P_POSITION_ID			IN NUMBER	DEFAULT NULL,
2211 			P_GRADE_ID			IN NUMBER	DEFAULT NULL,
2212 			P_LOCATION_ID			IN NUMBER	DEFAULT NULL,
2213 			P_EMPLOYMENT_CATEGORY		IN VARCHAR2	DEFAULT NULL,
2214 			P_PAYROLL_ID			IN NUMBER	DEFAULT NULL,
2215 			P_LINK_TO_ALL_PAYROLLS_FLAG	IN VARCHAR2	DEFAULT 'N',
2216 			P_PAY_BASIS_ID			IN NUMBER	DEFAULT NULL,
2217 			P_BUSINESS_GROUP_ID		IN NUMBER,
2218 			P_EFFECTIVE_DATE		IN DATE		DEFAULT hr_api.g_sys,
2219 			P_ERROR_WHEN_NOT_EXIST		IN VARCHAR2	DEFAULT 'TRUE') RETURN NUMBER
2220 --------------------------------------------------------------------------------
2221 	IS
2222 	BEGIN
2223 		return element_link_rec(
2224 				p_element_type_id,p_organization_id,p_people_group_id,p_job_id,p_position_id,
2225 				p_grade_id,p_location_id,p_employment_category,p_payroll_id,p_link_to_all_payrolls_flag,
2226 				p_pay_basis_id,p_business_group_id,p_effective_date,p_error_when_not_exist).element_link_id;
2227 	END;
2228 --------------------------------------------------------------------------------
2229 	FUNCTION backpay_rule_rec(
2230 			P_BACKPAY_SET_ID		IN NUMBER,
2231 			P_DEFINED_BALANCE_ID		IN NUMBER,
2232 			P_INPUT_VALUE_ID		IN NUMBER,
2233 			P_ERROR_WHEN_NOT_EXIST		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_BACKPAY_RULES%ROWTYPE
2234 --------------------------------------------------------------------------------
2235 	IS
2236 		l_unique_column_tbl	UniColTbl;
2237 		l_csr			RefCsr;
2238 		l_rec			PAY_BACKPAY_RULES%ROWTYPE;
2239 	BEGIN
2240 		unique_tbl_constructor('BACKPAY_SET_ID','N',to_char(p_backpay_set_id),l_unique_column_tbl);
2241 		unique_tbl_constructor('DEFINED_BALANCE_ID','N',to_char(p_defined_balance_id),l_unique_column_tbl);
2242 		unique_tbl_constructor('INPUT_VALUE_ID','N',to_char(p_input_value_id),l_unique_column_tbl);
2243 		l_csr := csr('PAY_BACKPAY_RULES',l_unique_column_tbl);
2244 		fetch l_csr into l_rec;
2245 		if l_csr%NOTFOUND then
2246 			if p_error_when_not_exist = 'TRUE' then
2247 				close l_csr;
2248 				fnd_message.set_name('PER','HR_JP_ID_NOT_FOUND');
2249 				fnd_message.set_token('SQL',g_sql);
2250 				fnd_message.raise_error;
2251 			else
2252 				l_rec := NULL;
2253 			end if;
2254 		end if;
2255 		close l_csr;
2256 
2257 		return l_rec;
2258 	END;
2259 --------------------------------------------------------------------------------
2260 	FUNCTION org_pay_method_usage_rec(
2261 			P_PAYROLL_ID			IN NUMBER,
2262 			P_ORG_PAYMENT_METHOD_ID		IN NUMBER,
2263 			P_EFFECTIVE_DATE		IN DATE		DEFAULT hr_api.g_sys,
2264 			P_ERROR_WHEN_NOT_EXIST		IN VARCHAR2	DEFAULT 'TRUE') RETURN PAY_ORG_PAY_METHOD_USAGES_F%ROWTYPE
2265 --------------------------------------------------------------------------------
2266 	IS
2267 		l_unique_column_tbl	UniColTbl;
2268 		l_csr			RefCsr;
2269 		l_rec			PAY_ORG_PAY_METHOD_USAGES_F%ROWTYPE;
2270 	BEGIN
2271 		unique_tbl_constructor('PAYROLL_ID','N',to_char(p_payroll_id),l_unique_column_tbl);
2272 		unique_tbl_constructor('ORG_PAYMENT_METHOD_ID','N',to_char(p_org_payment_method_id),l_unique_column_tbl);
2273 		l_csr := csr('PAY_ORG_PAY_METHOD_USAGES_F',l_unique_column_tbl,'DATETRACKED',p_effective_date);
2274 		fetch l_csr into l_rec;
2275 		if l_csr%NOTFOUND then
2276 			if p_error_when_not_exist = 'TRUE' then
2277 				close l_csr;
2278 				fnd_message.set_name('PER','HR_JP_ID_NOT_FOUND');
2279 				fnd_message.set_token('SQL',g_sql);
2280 				fnd_message.raise_error;
2281 			else
2282 				l_rec := NULL;
2283 			end if;
2284 		end if;
2285 		close l_csr;
2286 
2287 		return l_rec;
2288 	END;
2289 end;