1 package body PAY_PAYWSDAS_PKG as
2 /* $Header: pywsdas1.pkb 120.1 2005/12/23 02:43:19 arashid noship $ */
3 --
4 /*
5 * NAME
6 * core_fetch_dbi_info
7 *
8 * DESCRIPTION
9 * Internal routine containing common code to fetch database item
10 * information.
11 */
12 procedure core_fetch_dbi_info
13 (p_business_group_id in number
14 ,p_legislation_code in varchar2
15 ,p_formula_type_id in number
16 ,p_operand_value in varchar2
17 ,p_data_type out nocopy varchar2
18 ,p_null_allowed out nocopy varchar2
19 ,p_notfound_allowed out nocopy varchar2
20 ) is
21 cursor csr_dbitl(p_operand_value in varchar2) is
22 select dbitl.user_name
23 , dbitl.user_entity_id
24 from ff_database_items_tl dbitl
25 where dbitl.translated_user_name = p_operand_value
26 ;
27 begin
28 --
29 -- Exceptions will be passed up for handling in the calling code.
30 --
31 begin
32 select di.data_type
33 , di.null_allowed_flag
34 , ue.notfound_allowed_flag
35 into p_data_type
36 , p_null_allowed
37 , p_notfound_allowed
38 from ff_database_items di
39 , ff_user_entities ue
40 , ff_routes fr
41 where di.user_name = p_operand_value
42 and ue.user_entity_id = di.user_entity_id
43 and (
44 (ue.business_group_id is null and ue.legislation_code is null) or
45 ue.legislation_code = p_legislation_code or
46 ue.business_group_id = p_business_group_id
47 )
48 and fr.route_id = ue.route_id
49 and not exists
50 (
51 select context_id
52 from ff_route_context_usages rcu
53 where rcu.route_id = fr.route_id
54 minus
55 select context_id
56 from ff_ftype_context_usages fcu
57 where fcu.formula_type_id = p_formula_type_id
58 )
59 ;
60
61 --
62 -- Got a match.
63 --
64 return;
65 exception
66 when no_data_found then
67 if ff_dbi_utils_pkg.translations_supported(p_legislation_code) then
68 --
69 -- For the translated database item case use a cursor FOR-loop to return
70 -- the tiny fraction of rows from ff_database_items_tl. The code can then
71 -- match against ff_database_items, ff_user_entities etc. more efficiently.
72 --
73 for dbitl in csr_dbitl(p_operand_value => p_operand_value) loop
74 begin
75 select di.data_type
76 , di.null_allowed_flag
77 , ue.notfound_allowed_flag
78 into p_data_type
79 , p_null_allowed
80 , p_notfound_allowed
81 from ff_database_items di
82 , ff_user_entities ue
83 , ff_routes fr
84 where di.user_name = dbitl.user_name
85 and di.user_entity_id = dbitl.user_entity_id
86 and ue.user_entity_id = dbitl.user_entity_id
87 and (
88 (ue.business_group_id is null and ue.legislation_code is null) or
89 ue.legislation_code = p_legislation_code or
90 ue.business_group_id = p_business_group_id
91 )
92 and fr.route_id = ue.route_id
93 and not exists
94 (
95 select context_id
96 from ff_route_context_usages rcu
97 where rcu.route_id = fr.route_id
98 minus
99 select context_id
100 from ff_ftype_context_usages fcu
101 where fcu.formula_type_id = p_formula_type_id
102 )
103 ;
104
105 --
106 -- Got a match.
107 --
108 return;
109 exception
110 --
111 -- The user entity does not belong to the business group or legislation.
112 --
113 when no_data_found then
114 null;
115 end;
116 end loop;
117
118 --
119 -- No translated database item match.
120 --
121 raise no_data_found;
122 else
123 --
124 -- Translations are not supported, and a match was not made.
125 --
126 raise no_data_found;
127 end if;
128 end;
129 end core_fetch_dbi_info;
130 --
131 function get_formula_type return number is
132 --
133 cursor C_FID1 is
134 select FORMULA_TYPE_ID
135 from FF_FORMULA_TYPES
136 where upper(FORMULA_TYPE_NAME) = 'ASSIGNMENT SET';
137 --
138 formula_id FF_FORMULA_TYPES.FORMULA_TYPE_ID%type;
139 --
140 begin
141 --
142 open C_FID1;
143 fetch C_FID1 into formula_id;
144 --
145 if C_FID1%notfound then
146 close C_FID1;
147 fnd_message.set_name('PAY','HR_6153_ALL_PROCEDURE_FAIL');
148 fnd_message.set_token('PACKAGE','PAYWSDAS');
149 fnd_message.set_token('FUNCTION','GET_FORMULA_TYPE');
150 fnd_message.raise_error;
151 end if;
152 --
153 close C_FID1;
154 return(formula_id);
155 end;
156 --
157 --
158 function get_assignment_sets_s return number is
159 --
160 cursor C_ASS1 is
161 select HR_ASSIGNMENT_SETS_S.nextval
162 from DUAL;
163 --
164 ass_sets number(15);
165 --
166 begin
167 --
168 open C_ASS1;
169 fetch C_ASS1 into ass_sets;
170 if C_ASS1%notfound then
171 close C_ASS1;
172 fnd_message.set_name('PAY','HR_6153_ALL_PROCEDURE_FAIL');
173 fnd_message.set_token('PACKAGE','PAYWSDAS');
174 fnd_message.set_token('FUNCTION','GET_ASSIGNMENT_SETS_S');
175 fnd_message.raise_error;
176 else
177 close C_ASS1;
178 return(ass_sets);
179 end if;
180 end;
181 --
182 --
183 function get_formula_id(p_assignment_set_id in number) return number is
184 --
185 cursor C_FID2 is
186 select FORMULA_ID
187 from HR_ASSIGNMENT_SETS
188 where ASSIGNMENT_SET_ID = p_assignment_set_id;
189 --
190 formula_id HR_ASSIGNMENT_SETS.FORMULA_ID%type;
191 --
192 begin
193 --
194 open C_FID2;
195 fetch C_FID2 into formula_id;
196 --
197 if C_FID2%notfound then
198 close C_FID2;
199 fnd_message.set_name('PAY','HR_6153_ALL_PROCEDURE_FAIL');
200 fnd_message.set_token('PROCEDURE','PAYWSDAS');
201 fnd_message.set_token('STEP','GET_FORMULA_ID');
202 fnd_message.raise_error;
203 end if;
204 --
205 close C_FID2;
206 return(formula_id);
207 end;
208 --
209 --
210 function no_criteria_exists(p_assignment_set_id in number) return boolean is
211 --
212 cursor C_CR2 is
213 select null
214 from HR_ASSIGNMENT_SET_CRITERIA
215 where ASSIGNMENT_SET_ID = p_assignment_set_id;
216 --
217 dummy varchar2(1);
218 --
219 begin
220 --
221 open C_CR2;
222 fetch C_CR2 into dummy;
223 --
224 if C_CR2%found then
225 close C_CR2;
226 return(FALSE);
227 else
228 close C_CR2;
229 return(TRUE);
230 end if;
231 --
232 end;
233 --
234 --
235 procedure check_amendment_exists(p_assignment_set_id in number) is
236 --
237 cursor C_AMD2 is
238 select null
239 from HR_ASSIGNMENT_SET_AMENDMENTS
240 where ASSIGNMENT_SET_ID = p_assignment_set_id;
241 --
242 dummy varchar2(1);
243 --
244 begin
245 --
246 open C_AMD2;
247 fetch C_AMD2 into dummy;
248 --
249 if C_AMD2%found then
250 close C_AMD2;
251 fnd_message.set_name('PAY','HR_6941_PAY_AMENDMENTS_EXIST');
252 fnd_message.raise_error;
253 end if;
254 --
255 close C_AMD2;
256 end;
257 --
258 --
259 procedure check_unq_amendment(p_assignment_set_id in number,
260 p_assignment_id in number,
261 p_rowid in varchar2) is
262 --
263 cursor C_AMD5 is
264 select null
265 from HR_ASSIGNMENT_SET_AMENDMENTS
266 where ASSIGNMENT_SET_ID = p_assignment_set_id
267 and ASSIGNMENT_ID = p_assignment_id
268 and ( p_rowid is null
269 or p_rowid is not null and p_rowid <> ROWID);
270 --
271 dummy varchar2(1);
272 --
273 begin
274 --
275 open C_AMD5;
276 fetch C_AMD5 into dummy;
277 --
278 if C_AMD5%found then
279 close C_AMD5;
280 fnd_message.set_name('PAY','HR_6942_PAY_DUPLICATE_AMEND');
281 fnd_message.raise_error;
282 end if;
283 --
284 close C_AMD5;
285 end;
286 --
287 --
288 procedure check_amd_inc_exc(p_assignment_set_id in number) is
289 --
290 cursor C_AMD3 is
291 select null
292 from HR_ASSIGNMENT_SET_AMENDMENTS HR1,
293 HR_ASSIGNMENT_SET_AMENDMENTS HR2
294 where HR1.ASSIGNMENT_SET_ID = p_assignment_set_id
295 and HR1.ASSIGNMENT_SET_ID = HR2.ASSIGNMENT_SET_ID
296 and HR1.INCLUDE_OR_EXCLUDE <> HR2.INCLUDE_OR_EXCLUDE;
297 --
298 dummy varchar2(1);
299 --
300 begin
301 --
302 open C_AMD3;
303 fetch C_AMD3 into dummy;
304 --
305 if C_AMD3%found then
306 close C_AMD3;
307 fnd_message.set_name('PAY','HR_6944_PAY_DIFFERENT_AMEND');
308 fnd_message.raise_error;
309 end if;
310 --
311 close C_AMD3;
312 end;
313 --
314 --
315 procedure check_include_exclude(p_assignment_set_id in number,
316 p_include_exclude in varchar2,
317 p_rowid in varchar2) is
318 --
319 cursor C_AMD4 is
320 select null
321 from HR_ASSIGNMENT_SET_AMENDMENTS
322 where ASSIGNMENT_SET_ID = p_assignment_set_id
323 and INCLUDE_OR_EXCLUDE <> p_include_exclude
324 and ( p_rowid is null
325 or p_rowid is not null and p_rowid <> ROWID);
326 --
327 dummy varchar2(1);
328 begin
329 --
330 open C_AMD4;
331 fetch C_AMD4 into dummy;
332 --
333 if C_AMD4%found then
334 close C_AMD4;
335 fnd_message.set_name('PAY','HR_6943_PAY_NOT_INC_OR_EXC');
336 fnd_message.raise_error;
337 end if;
338 --
339 close C_AMD4;
340 end;
341 --
342 --
343 procedure check_criteria_exists(p_assignment_set_id in number,
344 p_line_no in number default 0) is
345 --
346 cursor C_AS2 is
347 select null
348 from HR_ASSIGNMENT_SET_CRITERIA
349 where ASSIGNMENT_SET_ID = p_assignment_set_id
350 and LINE_NO <> p_line_no;
351 --
352 dummy varchar2(1);
353 --
354 begin
355 --
356 open C_AS2;
357 fetch C_AS2 into dummy;
358 --
359 if C_AS2%found then
360 close C_AS2;
361 fnd_message.set_name('PAY','HR_6831_ASS_DEL_SET_CRIT');
362 fnd_message.raise_error;
363 end if;
364 --
365 close C_AS2;
366 end;
367 --
368 --
369 procedure check_operand(p_business_group_id in number,
370 p_legislation_code in varchar2,
371 p_formula_type_id in number,
372 p_data_type in out nocopy varchar2,
373 p_operand in varchar2) is
374 --
375 l_data_type ff_database_items.data_type%type;
376 l_null_allowed ff_database_items.null_allowed_flag%type;
377 l_notfound_allowed ff_user_entities.notfound_allowed_flag%type;
378 begin
379 --
380 begin
381 core_fetch_dbi_info
382 (p_business_group_id => p_business_group_id
383 ,p_legislation_code => p_legislation_code
384 ,p_formula_type_id => p_formula_type_id
385 ,p_operand_value => p_operand
386 ,p_data_type => l_data_type
387 ,p_null_allowed => l_null_allowed
388 ,p_notfound_allowed => l_notfound_allowed
389 );
390
391 --
392 -- Supplied data type should match - the original code included
393 -- data type matching claused in the SQL.
394 --
395 if l_data_type <> nvl(p_data_type, l_data_type) then
396 raise no_data_found;
397 end if;
398 exception
399 when no_data_found then
400 fnd_message.set_name('PAY','HR_6829_ASS_OPERAND_TYPE_MATCH');
401 fnd_message.raise_error;
402 end;
403 --
404 -- if procedure used to fetch data type
405 if p_data_type is null then
406 p_data_type := l_data_type;
407 end if;
408 end;
409 --
410 --
411 procedure check_unique_name(p_assignment_set_name in varchar2,
412 p_business_group_id in number,
413 p_rowid in varchar2,
414 p_formula_type_id in number,
415 p_legislation_code in varchar2) is
416 --
417 cursor C_CU1 is
418 select null
419 from HR_ASSIGNMENT_SETS
420 where upper(ASSIGNMENT_SET_NAME) = upper(p_assignment_set_name)
421 and business_group_id + 0 = p_business_group_id
422 and ( p_rowid is null
423 or
424 ( p_rowid is not null and ROWID <> p_rowid));
425 --
426 dummy varchar2(1);
427 d_assign_name varchar2(80);
428 --
429 begin
430 --
431 open C_CU1;
432 fetch C_CU1 into dummy;
433 if C_CU1%found then
434 close C_CU1;
435 fnd_message.set_name('PAY','HR_6395_SETUP_SET_EXISTS');
436 fnd_message.raise_error;
437 else
438 close C_CU1;
439 d_assign_name := p_assignment_set_name;
440 ffdict.validate_formula(d_assign_name,
441 p_formula_type_id,
442 p_business_group_id,
443 p_legislation_code);
444 end if;
445 --
446 end;
447 --
448 --
449 procedure check_line_no(p_assignment_set_id in number,
450 p_line_no in number,
451 p_rowid in varchar2) is
452 --
453 cursor C_LN1 is
454 select 'x'
455 from HR_ASSIGNMENT_SET_CRITERIA
456 where ASSIGNMENT_SET_ID = p_assignment_set_id
457 and LINE_NO = p_line_no
458 and (p_rowid is null
459 or p_rowid is not null and p_rowid <> ROWID);
460 --
461 dummy varchar2(1);
462 begin
463 --
464 open C_LN1;
465 fetch C_LN1 into dummy;
466 --
467 -- if row found then error
468 if C_LN1%found then
469 close C_LN1;
470 fnd_message.set_name('PAY','HR_6820_ASS_UNIQUE_SEQUENCE');
471 fnd_message.raise_error;
472 end if;
473 --
477 --
474 close C_LN1;
475 end;
476 --
478 procedure delete_formula(p_formula_id in number,
479 p_formula_type_id in number) is
480 --
481 begin
482 --
483 delete
484 from FF_FORMULAS_F
485 where FORMULA_ID = p_formula_id
486 and FORMULA_TYPE_ID = p_formula_type_id;
487 end;
488 --
489 --
490 procedure get_min_max_line(p_assignment_set_id in number,
491 p_min_line_no in out nocopy number,
492 p_max_line_no in out nocopy number) is
493 --
494 cursor C_MMAX1 is
495 select ((FLOOR(max(LINE_NO) / 10)) + 1) * 10,
496 min(LINE_NO)
497 from HR_ASSIGNMENT_SET_CRITERIA
498 where ASSIGNMENT_SET_ID = p_assignment_set_id;
499 --
500 begin
501 --
502 open C_MMAX1;
503 fetch C_MMAX1 into p_max_line_no,
504 p_min_line_no;
505 --
506 if C_MMAX1%notfound then
507 p_max_line_no := 10;
508 p_min_line_no := 0;
509 end if;
510
511 close C_MMAX1;
512 --
513 end;
514 --
515 procedure fetch_dbi_info
516 (p_assignment_set_id in number
517 ,p_formula_type_id in number
518 ,p_date_format in varchar2
519 ,p_operand_value in varchar2
520 ,p_data_type out nocopy varchar2
521 ,p_null_allowed out nocopy varchar2
522 ,p_notfound_allowed out nocopy varchar2
523 ,p_start_of_time out nocopy varchar2
524 ) is
525 l_business_group_id per_business_groups_perf.business_group_id%type;
526 l_legislation_code per_business_groups_perf.legislation_code%type;
527 begin
528 --
529 -- This code replaces plain SQL so don't worry about exceptions.
530 --
531
532 --
533 -- Fetch BUSINESS_GROUP_ID and LEGISLATION_CODE for the core routine call.
534 --
535 select bg.business_group_id
536 , bg.legislation_code
537 into l_business_group_id
538 , l_legislation_code
539 from hr_assignment_sets a_s
540 , per_business_groups_perf bg
541 where a_s.assignment_set_id = p_assignment_set_id
542 and bg.business_group_id = a_s.business_group_id
543 ;
544
545 --
546 -- Call the core routine.
547 --
548 core_fetch_dbi_info
549 (p_business_group_id => l_business_group_id
550 ,p_legislation_code => l_legislation_code
551 ,p_formula_type_id => p_formula_type_id
552 ,p_operand_value => p_operand_value
553 ,p_data_type => p_data_type
554 ,p_null_allowed => p_null_allowed
555 ,p_notfound_allowed => p_notfound_allowed
556 );
557
558 --
559 -- Return the start-of-time string in the required date format.
560 --
561 p_start_of_time :=
562 to_char(to_date('01/01/0001', 'DD/MM/YYYY'), p_date_format);
563 end fetch_dbi_info;
564 --
565 end PAY_PAYWSDAS_PKG;
[Home] [Help]