[Home] [Help]
PACKAGE BODY: APPS.PQH_RBC_UTILITY
Source
1 PACKAGE BODY PQH_RBC_UTILITY AS
2 /* $Header: pqrbcutl.pkb 120.35.12010000.2 2009/12/29 07:07:44 kgowripe ship $ */
3
4 function future_criteria_exist(p_copy_entity_txn_id in number) return varchar2 is
5 l_rate_matrix_id number(30);
6
7 cursor c11(p_rate_matrix_id in number) is select distinct criteria_short_code
8 from pqh_rate_matrix_nodes a, ben_eligy_prfl_f b
9 where pl_id = p_rate_matrix_id
10 and criteria_short_code is not null
11 and a.eligy_prfl_id = b.eligy_prfl_id;
12
13
14 cursor c21 is select distinct information13 from ben_copy_entity_results
15 where copy_entity_txn_id = p_copy_entity_txn_id
16 and table_alias = 'RBC_CRIT'; -- also consider 'DELETE' status rows
17
18 l_short_code varchar2(100);
19 l_temp varchar2(100);
20 l_status varchar2(10);
21 l_temp_found varchar2(10);
22 begin
23 l_status := 'NO';
24 l_temp_found := 'NO';
25
26 select information1 into l_rate_matrix_id from ben_copy_entity_results
27 where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'PLN';
28
29 open c11(l_rate_matrix_id);
30 loop
31 fetch c11 into l_short_code;
32 EXIT WHEN c11%NOTFOUND;
33 l_temp_found := 'NO';
34 -- check if a short code in base table is present in ben_copy
35 open c21;
36 loop
37 fetch c21 into l_temp;
38 EXIT WHEN c21%NOTFOUND;
39 if l_short_code = l_temp then
40 l_temp_found := 'YES';
41 exit;
42 end if;
43 end loop;
44 close c21;
45
46 if l_temp_found = 'NO' then
47 l_status := 'YES';
48 exit;
49 end if;
50
51 end loop;
52 close c11;
53
54 return l_status;
55
56 end future_criteria_exist;
57
58 FUNCTION allow_criteria_delete(p_eligy_criteria_id NUMBER) RETURN varchar2 IS
59 l_eligy_prfl_id number;
60 l_criteria_type varchar(100);
61 l_status varchar2(20);
62 cursor c1 is select eligy_prfl_id from ben_eligy_crit_values_f
63 where eligy_criteria_id = p_eligy_criteria_id;
64
65 BEGIN
66 open c1;
67 fetch c1 into l_eligy_prfl_id;
68 if c1%notfound then
69 l_status := 'DeleteEnabled';
70 else
71 l_status := 'DeleteDisabled';
72 end if;
73 close c1;
74 return l_status;
75 END allow_criteria_delete;
76
77 --
78 --
79 procedure delete_matrix_values(p_copy_entity_txn_id in number,
80 p_rate_matrix_node_id in number,
81 p_mode in varchar2
82 ) is
83 begin
84 hr_utility.set_location('going for deleting matrix values row',100);
85 /*
86 if p_mode = 'CREATE' then
87 delete from ben_copy_entity_results where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMV';
88 else
89 update ben_copy_entity_results set dml_operation = 'DELETE' where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMV';
90 end if;
91 */ -- dont use this because each row has its characteristics to revert back independent of pmode
92
93 delete from ben_copy_entity_results where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMV' and dml_operation = 'CREATE';
94 update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode = 'DELETE' where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMV' and dml_operation in ('UPDATE','COPIED');
95
96 exception
97 when others then
98 hr_utility.set_location('issues in deleting matrix values row',100);
99 raise;
100 end delete_matrix_values;
101
102 procedure delete_matrix_rates(p_copy_entity_txn_id in number,
103 p_rate_matrix_node_id in number,
104 p_mode in varchar2
105 ) is
106 begin
107 hr_utility.set_location('going for deleting matrix rates row',100);
108 /*
109 if p_mode = 'CREATE' then
110 delete from ben_copy_entity_results where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMR';
111 else
112 update ben_copy_entity_results set dml_operation = 'DELETE' where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMR';
113 end if;
114 */
115 delete from ben_copy_entity_results where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMR' and dml_operation = 'CREATE';
116 update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode = 'DELETE' where INFORMATION161 = p_rate_matrix_node_id and copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMR' and dml_operation in ('UPDATE','COPIED');
117
118
119 exception
120 when others then
121 hr_utility.set_location('issues in deleting matrix rates row',100);
122 raise;
123 end delete_matrix_rates;
124
125
126
127 procedure delete_matrix_nodes(p_copy_entity_txn_id in number,
128 p_pl_id in number,
129 p_level in number,
130 p_short_code in varchar2,
131 p_mode in varchar2
132
133 ) is
134 l_rate_matrix_node_id number;
135
136 -- p_mode in UPDATE or CREATE
137 cursor c1 is select information1 from ben_copy_entity_results where
138 Copy_entity_txn_id = p_copy_entity_txn_id and
139 Information160 = p_level and
140 information13 = p_short_code and
141 Information261 = p_pl_id and
142 TABLE_ALIAS = 'RMN';
143 begin
144 hr_utility.set_location('going for deleting matrix nodes row',100);
145
146 -- USE THIS IF YOU ARE NOT CALLING VOROW.REMOVE IN AM
147 delete from ben_copy_entity_results where
148 Copy_entity_txn_id = p_copy_entity_txn_id and
149 Information160 = p_level and
150 information13 = p_short_code and
151 Information261 = p_pl_id and
152 TABLE_ALIAS = 'RBC_CRIT' and
153 dml_operation = 'CREATE';
154
155 update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode = 'DELETE' where
156 Copy_entity_txn_id = p_copy_entity_txn_id and
157 Information160 = p_level and
158 information13 = p_short_code and
159 Information261 = p_pl_id and
160 TABLE_ALIAS = 'RBC_CRIT' and
161 dml_operation in ('UPDATE','COPIED');
162
163 hr_utility.set_location('deleting hgrid criteria rows',110);
164
165 open c1;
166 fetch c1 into l_rate_matrix_node_id;
167 if c1%notfound then
168 RETURN;
169 else
170 --delete all child rows of current rmn row
171 delete_matrix_values(p_copy_entity_txn_id,l_rate_matrix_node_id,p_mode);
172 delete_matrix_rates(p_copy_entity_txn_id,l_rate_matrix_node_id,p_mode);
173 end if;
174 close c1;
175
176 --DELETE RMN CURRENT ROW AFTER DELETING CHILD
177 delete from ben_copy_entity_results where
178 Copy_entity_txn_id = p_copy_entity_txn_id and
179 Information160 = p_level and
180 information13 = p_short_code and
181 Information261 = p_pl_id and
182 TABLE_ALIAS = 'RMN' and
183 dml_operation = 'CREATE';
184
185 update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode = 'DELETE' where
186 Copy_entity_txn_id = p_copy_entity_txn_id and
187 Information160 = p_level and
188 information13 = p_short_code and
189 Information261 = p_pl_id and
190 TABLE_ALIAS = 'RMN' and
191 dml_operation in ('UPDATE','COPIED');
192
193 exception
194 when others then
195 hr_utility.set_location('issues in deleting matrix nodes row',100);
196 raise;
197 end delete_matrix_nodes;
198
199
200
201
202 /*
203 To check if there are criteria values present in rate matrix, check if there are any rows in ben_copy_entity_results with table_alias = 'RMN' and dml_operation <> 'DELETE' for the current copy_entity_txn_id.
204 To check if there are criteria attached to rate matrix, check if there are any rows in ben_copy_entity_results with table_alias = 'RBC_CRIT' and dml_operation <> 'DELETE' for the current copy_entity_txn_id.
205 */
206
207
208 function allow_hgrid_reorder(p_copy_entity_txn_id in number) return varchar2 IS
209 l_temp varchar2(1);
210 l_max number;
211
212 cursor c1 is select null from ben_copy_entity_results where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RMN' and dml_operation <> 'DELETE' and information160 <> 1;
213 cursor c2 is select null from ben_copy_entity_results where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RBC_CRIT' and dml_operation <> 'DELETE';
214 BEGIN
215
216 /* INSTEAD OF C2 WE CAN USE THIS
217 select max(nvl(information160,1)) into l_max from ben_copy_entity_results where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RBC_CRIT' and dml_operation <> 'DELETE';
218 if l_max = 1 then
219 return 'DisallowOrder';
220 end if;
221 */
222
223 if future_criteria_exist(p_copy_entity_txn_id) = 'YES' then
224 return 'DisallowOrder';
225 end if;
226
227
228 open c2;
229 fetch c2 into l_temp;
230 if c2%notfound then
231 close c2;
232 return 'DisallowOrder';
233 end if;
234 fetch c2 into l_temp;
235 if c2%notfound then
236 close c2;
237 return 'DisallowOrder';
238 end if;
239 close c2;
240
241 open c1;
242 fetch c1 into l_temp;
243 if c1%notfound then
244 close c1;
245 return 'AllowOrder';
246 else
247 close c1;
248 return 'DisallowOrder';
249 end if;
250 close c1;
251
252 END allow_hgrid_reorder;
253
254 FUNCTION get_matrix_disable_date(p_pl_id in number,p_effective_date in date) RETURN Date IS
255 l_effective_start_date Date;
256 cursor c1 is
257 select effective_start_date from ben_pl_f
258 where pl_id = p_pl_id
259 and pl_stat_cd = 'I'
260 and effective_start_date > p_effective_date
261 order by effective_start_date;
262 BEGIN
263 open c1;
264 fetch c1 into l_effective_start_date;
265 if c1%notfound then
266 RETURN null;
267 else
268 RETURN l_effective_start_date;
269 end if;
270 close c1;
271 END get_matrix_disable_date;
272
273
274 procedure create_criteria_txn(p_mode in varchar2,
275 p_business_area in varchar2,
276 p_business_group_id in number,
277 p_effective_date in date,
278 p_copy_entity_txn_id out nocopy number) is
279 l_rbc_txn_cat number;
280 l_ovn number;
281 begin
282 hr_utility.set_location('going for creating cet row',100);
283 begin
284 select transaction_category_id
285 into l_rbc_txn_cat
286 from pqh_transaction_categories
287 where short_name ='CRITERIA'
288 and business_group_id is null;
289 hr_utility.set_location('txn_cat is'||l_rbc_txn_cat,100);
290 exception
291 when others then
292 hr_utility.set_location('txn_cat doesnot exist',100);
293 raise;
294 end;
295 if l_rbc_txn_cat is not null then
296 pqh_copy_entity_txns_api.create_COPY_ENTITY_TXN
297 (p_copy_entity_txn_id => p_copy_entity_txn_id
298 ,p_transaction_category_id => l_rbc_txn_cat
299 ,p_context_business_group_id => p_business_group_id
300 ,p_context => 'CRITERIA'
301 ,p_action_date => p_effective_date
302 ,p_number_of_copies => 1
303 ,p_display_name => p_mode||' - CRITERIA - '||to_char(sysdate,'ddmmyyyyhhmiss')
304 ,p_replacement_type_cd => 'NONE'
305 ,p_start_with => p_business_area
306 ,p_status => p_mode
307 ,p_object_version_number => l_ovn
308 ,p_effective_date => p_effective_date
309 ) ;
310 end if;
311 exception
312 when others then
313 hr_utility.set_location('issues in creating CET row',100);
314 raise;
315 end create_criteria_txn;
316 /*
317 procedure create_criteria_cer (p_copy_entity_txn_id in number,
318 p_effective_date in date,
319 p_business_group_id in number,
320 p_crit_cer_id out nocopy number,
321 p_crit_cer_ovn out nocopy number) is
322 l_egl_tr_id number;
323 l_egl_tr_name varchar2(80);
324 begin
325 pqh_gsp_hr_to_stage.get_table_route_details
326 (p_table_alias => 'EGL',
327 p_table_route_id => l_egl_tr_id,
328 p_table_name => l_egl_tr_name);
329
330 ben_copy_entity_results_api.create_copy_entity_results(
331 p_effective_date => p_effective_date
332 ,p_copy_entity_txn_id => p_copy_entity_txn_id
333 ,p_result_type_cd => 'DISPLAY'
334 ,p_table_name => l_egl_tr_name
335 ,p_table_route_id => l_egl_tr_id
336 ,p_table_alias => 'EGL'
337 ,p_dml_operation => 'CREATE'
338 ,p_information2 => p_effective_date
339 ,p_information4 => p_business_group_id
340 ,p_information12 => 'USER'
341 ,p_copy_entity_result_id => p_crit_cer_id
342 ,p_object_version_number => p_crit_cer_ovn);
343 end create_criteria_cer;
344 */
345
346 procedure check_criteria_in_busgrp(p_eligy_criteria_id_std in number,p_business_group_id in number,p_eligy_criteria_id_new out nocopy number) is
347 l_short_code varchar2(240);
348 l_eligy_criteria_id_new number;
349 cursor c1 is select short_code from ben_eligy_criteria
350 where eligy_criteria_id = p_eligy_criteria_id_std
351 and business_group_id is null;
352
353 cursor c2(p_short_code varchar2) is
354 select eligy_criteria_id from ben_eligy_criteria
355 where short_code = p_short_code
356 and business_group_id = p_business_group_id;
357
358 begin
359
360 open c1;
361 fetch c1 into l_short_code;
362 if c1%notfound then
363 p_eligy_criteria_id_new := p_eligy_criteria_id_std;
364 else
365 open c2(l_short_code);
366 fetch c2 into l_eligy_criteria_id_new;
367 if c2%notfound then
368 p_eligy_criteria_id_new := p_eligy_criteria_id_std;
369 else
370 p_eligy_criteria_id_new :=l_eligy_criteria_id_new;
371 end if;
372
373 close c2;
374
375
376 end if;
377 close c1;
378
379 end check_criteria_in_busgrp;
380
381 -- copy every thing similar to copy_egl_row but keep our business_group_id,'CREATE" dml operation
382 procedure copy_criteria_std (p_copy_entity_txn_id in number,
383 p_effective_date in date,
384 p_business_group_id in number,
385 p_eligy_criteria_id in number,
386 p_copy_entity_result_id out nocopy number,
387 p_copy_entity_result_ovn out nocopy number) is
388
389 l_flex_value_set_name varchar2(80);
390 cursor c1 is select * from ben_eligy_criteria
391 where eligy_criteria_id = p_eligy_criteria_id;
392
393 l_egl_tr_id number;
394 l_egl_tr_name varchar2(80);
395 l_egl_cer_id number;
396 l_egl_ovn number;
397 l_ben_eligy_criteria number;
398 begin
399 -- get new ben_eligy_criteria sequence --NOT NEEDED BCOZ WE USE API TO CREATE
400
401 select BEN_ELIGY_CRITERIA_S.NEXTVAL into l_ben_eligy_criteria from dual;
402
403 pqh_gsp_hr_to_stage.get_table_route_details
404 (p_table_alias => 'EGL',
405 p_table_route_id => l_egl_tr_id,
406 p_table_name => l_egl_tr_name);
407 hr_utility.set_location('table route is'||l_egl_tr_name,10);
408
409
410 for l_egl_rec in c1 loop
411 if l_egl_rec.col1_value_set_id is not null then
412 SELECT flex_value_set_name
413 into l_flex_value_set_name
414 FROM fnd_flex_value_sets
415 WHERE flex_value_set_id = l_egl_rec.col1_value_set_id;
416 hr_utility.set_location('value set name is'||l_flex_value_set_name,10);
417 else
418 hr_utility.set_location('value set is not used',10);
419 end if;
420
421 -- copy every thing similar to copy_egl_row but keep our business_group_id instead
422
423 ben_copy_entity_results_api.create_copy_entity_results (
424 p_copy_entity_result_id => l_egl_cer_id,
425 p_copy_entity_txn_id => p_copy_entity_txn_id,
426 p_result_type_cd => 'DISPLAY',
427 p_number_of_copies => 1,
428 p_table_route_id => l_egl_tr_id,
429 p_table_alias => 'EGL',
430 p_dml_operation => 'CREATE',
431 p_information1 => l_ben_eligy_criteria, -- get from sequence number
432 p_information4 => p_business_group_id, -- add our business group
433 p_information5 => l_egl_rec.name,
434 p_information11 => l_egl_rec.short_code,
435 p_information12 => l_egl_rec.criteria_type,
436 p_information13 => l_egl_rec.crit_col1_val_type_cd,
437 p_information14 => l_egl_rec.crit_col1_datatype,
438 p_information15 => l_egl_rec.col1_lookup_type,
439 p_information16 => l_egl_rec.access_table_name1,
440 p_information17 => l_egl_rec.access_column_name1,
441 p_information18 => l_egl_rec.time_entry_access_table_name1,
442 p_information19 => l_egl_rec.time_entry_access_col_name1,
443 p_information20 => l_egl_rec.crit_col2_val_type_cd,
444 p_information21 => l_egl_rec.crit_col2_datatype,
445 p_information22 => l_egl_rec.col2_lookup_type,
446 p_information23 => l_egl_rec.access_table_name2,
447 p_information24 => l_egl_rec.access_column_name2,
448 p_information25 => l_egl_rec.time_entry_access_table_name2,
449 p_information26 => l_egl_rec.time_entry_access_col_name2,
450 p_information27 => l_egl_rec.allow_range_validation_flag,
451 p_information28 => l_egl_rec.user_defined_flag,
452 p_information29 => l_egl_rec.legislation_code,
453 p_information110 => l_egl_rec.egl_attribute_category,
454 p_information111 => l_egl_rec.egl_attribute1,
455 p_information112 => l_egl_rec.egl_attribute2,
456 p_information113 => l_egl_rec.egl_attribute3,
457 p_information114 => l_egl_rec.egl_attribute4,
458 p_information115 => l_egl_rec.egl_attribute5,
459 p_information116 => l_egl_rec.egl_attribute6,
460 p_information117 => l_egl_rec.egl_attribute7,
461 p_information118 => l_egl_rec.egl_attribute8,
462 p_information119 => l_egl_rec.egl_attribute9,
463 p_information120 => l_egl_rec.egl_attribute10,
464 p_information121 => l_egl_rec.egl_attribute11,
465 p_information122 => l_egl_rec.egl_attribute12,
466 p_information123 => l_egl_rec.egl_attribute13,
467 p_information124 => l_egl_rec.egl_attribute14,
468 p_information125 => l_egl_rec.egl_attribute15,
469 p_information126 => l_egl_rec.egl_attribute16,
470 p_information127 => l_egl_rec.egl_attribute17,
471 p_information128 => l_egl_rec.egl_attribute18,
472 p_information129 => l_egl_rec.egl_attribute19,
473 p_information130 => l_egl_rec.egl_attribute20,
474 p_information131 => l_egl_rec.egl_attribute21,
475 p_information132 => l_egl_rec.egl_attribute22,
476 p_information133 => l_egl_rec.egl_attribute23,
477 p_information134 => l_egl_rec.egl_attribute24,
478 p_information135 => l_egl_rec.egl_attribute25,
479 p_information136 => l_egl_rec.egl_attribute26,
480 p_information137 => l_egl_rec.egl_attribute27,
481 p_information138 => l_egl_rec.egl_attribute28,
482 p_information139 => l_egl_rec.egl_attribute29,
483 p_information140 => l_egl_rec.egl_attribute30,
484 p_information170 => l_egl_rec.name,
485 p_information185 => l_flex_value_set_name,
486 p_information219 => l_egl_rec.description,
487 p_information265 => l_egl_rec.object_version_number,
488 p_information266 => l_egl_rec.col1_value_set_id,
489 p_information267 => l_egl_rec.col2_value_set_id,
490 p_information268 => l_egl_rec.access_calc_rule,
491 p_information30 => l_egl_rec.allow_range_validation_flag2,
492 p_information269 => l_egl_rec.access_calc_rule2,
493 p_information270 => l_egl_rec.time_access_calc_rule1,
494 p_information271 => l_egl_rec.time_access_calc_rule2,
495 p_object_version_number => l_egl_ovn,
496 p_effective_date => p_effective_date);
497 end loop;
498 p_copy_entity_result_id := l_egl_cer_id;
499 p_copy_entity_result_ovn := l_egl_ovn;
500 end copy_criteria_std;
501 --
502 procedure copy_egl_row(p_eligy_criteria_id in number,
503 p_copy_entity_txn_id in number,
504 p_effective_date in date,
505 p_copy_entity_result_id out nocopy number,
506 p_copy_entity_result_ovn out nocopy number) is
507
508 l_flex_value_set_name varchar2(80);
509 cursor c1 is select * from ben_eligy_criteria
510 where eligy_criteria_id = p_eligy_criteria_id;
511
512 l_egl_tr_id number;
513 l_egl_tr_name varchar2(80);
514 l_egl_cer_id number;
515 l_egl_ovn number;
516 begin
517 pqh_gsp_hr_to_stage.get_table_route_details
518 (p_table_alias => 'EGL',
519 p_table_route_id => l_egl_tr_id,
520 p_table_name => l_egl_tr_name);
521 hr_utility.set_location('table route is'||l_egl_tr_name,10);
522 for l_egl_rec in c1 loop
523 if l_egl_rec.col1_value_set_id is not null then
524 SELECT flex_value_set_name
525 into l_flex_value_set_name
526 FROM fnd_flex_value_sets
527 WHERE flex_value_set_id = l_egl_rec.col1_value_set_id;
528 hr_utility.set_location('value set name is'||l_flex_value_set_name,10);
529 else
530 hr_utility.set_location('value set is not used',10);
531 end if;
532 ben_copy_entity_results_api.create_copy_entity_results (
533 p_copy_entity_result_id => l_egl_cer_id,
534 p_copy_entity_txn_id => p_copy_entity_txn_id,
535 p_result_type_cd => 'DISPLAY',
536 p_number_of_copies => 1,
537 p_table_route_id => l_egl_tr_id,
538 p_table_alias => 'EGL',
539 p_dml_operation => 'UPDATE',
540 p_information1 => l_egl_rec.eligy_Criteria_id,
541 p_information4 => l_egl_rec.business_group_id,
542 p_information5 => l_egl_rec.name,
543 p_information11 => l_egl_rec.short_code,
544 p_information12 => l_egl_rec.criteria_type,
545 p_information13 => l_egl_rec.crit_col1_val_type_cd,
546 p_information14 => l_egl_rec.crit_col1_datatype,
547 p_information15 => l_egl_rec.col1_lookup_type,
548 p_information16 => l_egl_rec.access_table_name1,
549 p_information17 => l_egl_rec.access_column_name1,
550 p_information18 => l_egl_rec.time_entry_access_table_name1,
551 p_information19 => l_egl_rec.time_entry_access_col_name1,
552 p_information20 => l_egl_rec.crit_col2_val_type_cd,
553 p_information21 => l_egl_rec.crit_col2_datatype,
554 p_information22 => l_egl_rec.col2_lookup_type,
555 p_information23 => l_egl_rec.access_table_name2,
556 p_information24 => l_egl_rec.access_column_name2,
557 p_information25 => l_egl_rec.time_entry_access_table_name2,
558 p_information26 => l_egl_rec.time_entry_access_col_name2,
559 p_information27 => l_egl_rec.allow_range_validation_flag,
560 p_information28 => l_egl_rec.user_defined_flag,
561 p_information29 => l_egl_rec.legislation_code,
562 p_information110 => l_egl_rec.egl_attribute_category,
563 p_information111 => l_egl_rec.egl_attribute1,
564 p_information112 => l_egl_rec.egl_attribute2,
565 p_information113 => l_egl_rec.egl_attribute3,
566 p_information114 => l_egl_rec.egl_attribute4,
567 p_information115 => l_egl_rec.egl_attribute5,
568 p_information116 => l_egl_rec.egl_attribute6,
569 p_information117 => l_egl_rec.egl_attribute7,
570 p_information118 => l_egl_rec.egl_attribute8,
571 p_information119 => l_egl_rec.egl_attribute9,
572 p_information120 => l_egl_rec.egl_attribute10,
573 p_information121 => l_egl_rec.egl_attribute11,
574 p_information122 => l_egl_rec.egl_attribute12,
575 p_information123 => l_egl_rec.egl_attribute13,
576 p_information124 => l_egl_rec.egl_attribute14,
577 p_information125 => l_egl_rec.egl_attribute15,
578 p_information126 => l_egl_rec.egl_attribute16,
579 p_information127 => l_egl_rec.egl_attribute17,
580 p_information128 => l_egl_rec.egl_attribute18,
581 p_information129 => l_egl_rec.egl_attribute19,
582 p_information130 => l_egl_rec.egl_attribute20,
583 p_information131 => l_egl_rec.egl_attribute21,
584 p_information132 => l_egl_rec.egl_attribute22,
585 p_information133 => l_egl_rec.egl_attribute23,
586 p_information134 => l_egl_rec.egl_attribute24,
587 p_information135 => l_egl_rec.egl_attribute25,
588 p_information136 => l_egl_rec.egl_attribute26,
589 p_information137 => l_egl_rec.egl_attribute27,
590 p_information138 => l_egl_rec.egl_attribute28,
591 p_information139 => l_egl_rec.egl_attribute29,
592 p_information140 => l_egl_rec.egl_attribute30,
593 p_information170 => l_egl_rec.name,
594 p_information185 => l_flex_value_set_name,
595 p_information219 => l_egl_rec.description,
596 p_information265 => l_egl_rec.object_version_number,
597 p_information266 => l_egl_rec.col1_value_set_id,
598 p_information267 => l_egl_rec.col2_value_set_id,
599 p_information268 => l_egl_rec.access_calc_rule,
600 p_information30 => l_egl_rec.allow_range_validation_flag2,
601 p_information269 => l_egl_rec.access_calc_rule2,
602 p_information270 => l_egl_rec.time_access_calc_rule1,
603 p_information271 => l_egl_rec.time_access_calc_rule2,
604 p_object_version_number => l_egl_ovn,
605 p_effective_date => p_effective_date);
606 end loop;
607 p_copy_entity_result_id := l_egl_cer_id;
608 p_copy_entity_result_ovn := l_egl_ovn;
609 end copy_egl_row;
610 --
611 /**
612 procedure load_criteria_seed_row(
613 p_owner in varchar2
614 ,p_short_code in varchar2
615 ,p_name in varchar2
616 ,p_description in varchar2
617 ,p_crit_col1_val_type_cd in varchar2
618 ,p_crit_col1_datatype in varchar2
619 ,p_col1_lookup_type in varchar2
620 ,p_col1_value_set_name in varchar2
621 ,p_access_table_name1 in varchar2
622 ,p_access_column_name1 in varchar2
623 ,p_crit_col2_val_type_cd in varchar2
624 ,p_crit_col2_datatype in varchar2
625 ,p_col2_lookup_type in varchar2
626 ,p_col2_value_set_name in varchar2
627 ,p_access_table_name2 in varchar2
628 ,p_access_column_name2 in varchar2
629 ,p_allow_range_validation_flag in varchar2
630 ,p_allow_range_validation_flag2 in varchar2
631 ,p_user_defined_flag in varchar2
632 ,p_business_group_id in varchar2
633 ,p_legislation_code in varchar2
634 ) is
635
636 l_ovn number := 1;
637 l_created_by ben_eligy_criteria.created_by%type;
638 l_last_updated_by ben_eligy_criteria.last_updated_by%type;
639 l_creation_date ben_eligy_criteria.creation_date%type;
640 l_last_update_date ben_eligy_criteria.last_update_date%type;
641 l_last_update_login ben_eligy_criteria.last_update_login%type;
642
643 l_col1_value_set_id ben_eligy_criteria.col1_value_set_id%type;
644 l_col2_value_set_id ben_eligy_criteria.col2_value_set_id%type;
645
646 l_eligy_criteria_id ben_eligy_criteria.eligy_criteria_id%type;
647
648
649 cursor csr_fvs(p_valset_name in varchar2) is
650 select flex_value_set_id
651 from fnd_flex_value_sets
652 where flex_value_set_name = p_valset_name;
653 --
654 cursor csr_bec is
655 select eligy_criteria_id
656 from ben_eligy_criteria
657 where short_code = p_short_code and business_group_id is null and criteria_type='STD';
658 --
659 cursor csr_bg_bec is
660 select eligy_criteria_id
661 from ben_eligy_criteria
662 where short_code = p_short_code and business_group_id is not null and criteria_type='STD';
663 --
664 l_data_migrator_mode varchar2(10);
665 --
666 begin
667 --
668 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
669 hr_general.g_data_migrator_mode := 'Y';
670 --
671 l_last_updated_by := fnd_load_util.owner_id(p_owner);
672 l_created_by := fnd_load_util.owner_id(p_owner);
673 l_creation_date := sysdate;
674 l_last_update_date := sysdate;
675 l_last_update_login := 0;
676 --
677 open csr_bec;
678 fetch csr_bec into l_eligy_criteria_id;
679 close csr_bec;
680 --
681 open csr_fvs(p_col1_value_set_name);
682 fetch csr_fvs into l_col1_value_set_id;
683 close csr_fvs;
684 --
685 open csr_fvs(p_col2_value_set_name);
686 fetch csr_fvs into l_col2_value_set_id;
687 close csr_fvs;
688
689 if l_eligy_criteria_id is not null then
690 --
691 update ben_eligy_criteria set
692 name = p_name,
693 description = p_description,
694 crit_col1_val_type_cd = p_crit_col1_val_type_cd,
695 crit_col1_datatype = p_crit_col1_datatype,
696 col1_lookup_type = p_col1_lookup_type,
697 col1_value_set_id = l_col1_value_set_id,
698 access_table_name1 = p_access_table_name1 ,
699 access_column_name1 = p_access_column_name1,
700 crit_col2_val_type_cd = p_crit_col2_val_type_cd,
701 crit_col2_datatype = p_crit_col2_datatype,
702 col2_lookup_type = p_col2_lookup_type,
703 col2_value_set_id = l_col2_value_set_id,
704 access_table_name2 = p_access_table_name2,
705 access_column_name2 = p_access_column_name2,
706 allow_range_validation_flag = p_allow_range_validation_flag,
707 allow_range_validation_flag2 = p_allow_range_validation_flag2,
708 user_defined_flag = p_user_defined_flag,
709 business_group_id = to_number(p_business_group_id),
710 legislation_code = p_legislation_code,
711 criteria_type = 'STD',
712 last_updated_by = l_last_updated_by,
713 last_update_date = l_last_update_date,
714 last_update_login = l_last_update_login
715 where eligy_criteria_id = l_eligy_criteria_id;
716 --
717 -- Update any BG specific rows that were created.
718 --
719 For bg_crit_rec in csr_bg_bec loop
720 --
721 update ben_eligy_criteria set
722 name = p_name,
723 description = p_description,
724 crit_col1_val_type_cd = p_crit_col1_val_type_cd,
725 crit_col1_datatype = p_crit_col1_datatype,
726 col1_lookup_type = p_col1_lookup_type,
727 col1_value_set_id = l_col1_value_set_id,
728 access_table_name1 = p_access_table_name1 ,
729 access_column_name1 = p_access_column_name1,
730 crit_col2_val_type_cd = p_crit_col2_val_type_cd,
731 crit_col2_datatype = p_crit_col2_datatype,
732 col2_lookup_type = p_col2_lookup_type,
733 col2_value_set_id = l_col2_value_set_id,
734 access_table_name2 = p_access_table_name2,
735 access_column_name2 = p_access_column_name2,
736 allow_range_validation_flag = p_allow_range_validation_flag,
737 allow_range_validation_flag2 = p_allow_range_validation_flag2,
738 user_defined_flag = p_user_defined_flag,
739 legislation_code = p_legislation_code,
740 criteria_type = 'STD',
741 last_updated_by = l_last_updated_by,
742 last_update_date = l_last_update_date,
743 last_update_login = l_last_update_login
744 where eligy_criteria_id = bg_crit_rec.eligy_criteria_id;
745 --
746 End loop;
747
748 else
749 --
750 insert into ben_eligy_criteria
751 (
752 eligy_criteria_id,
753 short_code,
754 name,
755 description,
756 crit_col1_val_type_cd,
757 crit_col1_datatype,
758 col1_lookup_type,
759 col1_value_set_id,
760 access_table_name1,
761 access_column_name1,
762 crit_col2_val_type_cd,
763 crit_col2_datatype,
764 col2_lookup_type,
765 col2_value_set_id,
766 access_table_name2,
767 access_column_name2,
768 allow_range_validation_flag,
769 allow_range_validation_flag2,
770 user_defined_flag,
771 business_group_id,
772 legislation_code,
773 criteria_type,
774 created_by,
775 creation_date,
776 last_updated_by,
777 last_update_date,
778 last_update_login,
779 object_version_number
780 )
781 values
782 (
783 ben_eligy_criteria_s.nextval,
784 p_short_code,
785 p_name,
786 p_description,
787 p_crit_col1_val_type_cd,
788 p_crit_col1_datatype,
789 p_col1_lookup_type,
790 l_col1_value_set_id,
791 p_access_table_name1,
792 p_access_column_name1,
793 p_crit_col2_val_type_cd,
794 p_crit_col2_datatype,
795 p_col2_lookup_type,
796 l_col2_value_set_id,
797 p_access_table_name2,
798 p_access_column_name2,
799 p_allow_range_validation_flag,
800 p_allow_range_validation_flag2,
801 p_user_defined_flag,
802 null,
803 p_legislation_code,
804 'STD',
805 l_created_by,
806 l_creation_date,
807 l_last_updated_by,
808 l_last_update_date,
809 l_last_update_login,
810 l_ovn
811 );
812 --
813 end if;
814 --
815 hr_general.g_data_migrator_mode := l_data_migrator_mode;
816 end load_criteria_seed_row;
817 **/
818 --
819 procedure create_update_criteria(p_mode in varchar2,
820 p_eligy_criteria_id in number,
821 p_business_area in varchar2,
822 p_business_group_id in number,
823 p_effective_date in date,
824 p_criteria_type in varchar2,
825 p_copy_entity_txn_id in out nocopy number,
826 p_copy_entity_result_id out nocopy number,
827 p_copy_entity_result_ovn out nocopy number) is
828 l_cet_id number;
829 l_cer_id number;
830 l_cer_ovn number;
831 l_eligy_criteria_id_new number;
832 begin
833
834 if p_mode ='CREATE' and p_copy_entity_txn_id is null then
835 hr_utility.set_location('creating cet row',10);
836 create_criteria_txn(p_mode => p_mode,
837 p_business_area => p_business_area,
838 p_business_group_id => p_business_group_id,
839 p_effective_date => p_effective_date,
840 p_copy_entity_txn_id => l_cet_id);
841
842 if l_cet_id is not null then
843 hr_utility.set_location('populate out params',10);
844 p_copy_entity_txn_id := l_cet_id;
845 else
846 hr_utility.set_location('cet row is not there',10);
847 end if;
848
849 elsif p_mode ='UPDATE' and p_copy_entity_txn_id is null then
850 -- create the copy entity txn row
851 -- copy the EGL data into staging area and set dml_operation to 'UPDATE'
852 hr_utility.set_location('creating cet row for update',10);
853 create_criteria_txn(p_mode => p_mode,
854 p_business_area => p_business_area,
855 p_business_group_id => p_business_group_id,
856 p_effective_date => p_effective_date,
857 p_copy_entity_txn_id => l_cet_id);
858
859
860
861 if l_cet_id is not null then
862
863 if p_criteria_type = 'USER' then -- USER DEFINED CRITERIA TYPE
864 -- we have to pull eligy criteria into cer row
865 hr_utility.set_location('copying criteria row',10);
866 copy_egl_row(p_eligy_criteria_id => p_eligy_criteria_id,
867 p_copy_entity_txn_id => l_cet_id,
868 p_effective_date => p_effective_date,
869 p_copy_entity_result_id => l_cer_id,
870 p_copy_entity_result_ovn => l_cer_ovn);
871 else -- WE HAVE TO PULL STANDARD CRITERIA AND ADD BUSINESS GRP ID
872
873 check_criteria_in_busgrp(p_eligy_criteria_id,p_business_group_id,l_eligy_criteria_id_new);
874
875 hr_utility.set_location('Value returned from check_criteria_in_busgrp:'||l_eligy_criteria_id_new ,10);
876
877 if p_eligy_criteria_id <> l_eligy_criteria_id_new then
878 -- we have to pull eligy criteria into cer row
879 hr_utility.set_location('copying criteria row',10);
880 copy_egl_row(p_eligy_criteria_id => l_eligy_criteria_id_new,
881 p_copy_entity_txn_id => l_cet_id,
882 p_effective_date => p_effective_date,
883 p_copy_entity_result_id => l_cer_id,
884 p_copy_entity_result_ovn => l_cer_ovn);
885 else
886 hr_utility.set_location('copying criteria std row',10);
887 copy_criteria_std(p_eligy_criteria_id => p_eligy_criteria_id,
888 p_copy_entity_txn_id => l_cet_id,
889 p_effective_date => p_effective_date,
890 p_business_group_id => p_business_group_id,
891 p_copy_entity_result_id => l_cer_id,
892 p_copy_entity_result_ovn => l_cer_ovn);
893 end if;
894 end if;
895
896 if l_cer_id is not null then
897 hr_utility.set_location('populate out params',10);
898 p_copy_entity_txn_id := l_cet_id;
899 p_copy_entity_result_id := l_cer_id;
900 p_copy_entity_result_ovn := l_cer_ovn;
901 else
902 hr_utility.set_location('cer row not there',10);
903 end if;
904
905 end if;
906
907
908 else
909 hr_utility.set_location('invalid mode passed',10);
910 end if;
911 end create_update_criteria;
912
913
914 procedure stage_to_criteria(p_copy_entity_txn_id in number,
915 p_effective_date in date,
916 p_eligy_criteria_id out nocopy number) is
917 cursor c1 is select * from ben_copy_entity_results
918 where table_alias = 'EGL'
919 and copy_entity_txn_id = p_copy_entity_txn_id;
920 l_eligy_criteria_id number;
921 l_eligy_ovn number;
922 l_effective_date date := p_effective_date;
923 begin
924 FND_MSG_PUB.initialize;
925 for r_egl in c1 loop
926 if r_egl.dml_operation ='UPDATE' then
927 hr_utility.set_location('going for update operation ',10);
928 l_eligy_ovn := r_egl.information265;
929 l_eligy_criteria_id := r_egl.information1;
930
931 ben_eligy_criteria_api.update_eligy_criteria(
932 p_validate => FALSE,
933 p_eligy_criteria_id => l_eligy_criteria_id,
934 p_name => r_egl.information170,
935 p_short_code => r_egl.information11,
936 p_description => r_egl.information219,
937 p_criteria_type => r_egl.information12,
938 p_crit_col1_val_type_cd => r_egl.information13,
939 p_crit_col1_datatype => r_egl.information14,
940
941 p_col1_lookup_type => r_egl.information15,
942 p_col1_value_set_id => r_egl.information266,
943 p_access_table_name1 => r_egl.information16,
944 p_access_column_name1 => r_egl.information17,
945 p_time_entry_access_tab_nam1 => r_egl.information18,
946 p_time_entry_access_col_nam1 => r_egl.information19,
947
948 p_crit_col2_val_type_cd => r_egl.information20,
949 p_crit_col2_datatype => r_egl.information21,
950 p_col2_lookup_type => r_egl.information22,
951 p_col2_value_set_id => r_egl.information267,
952 p_access_table_name2 => r_egl.information23,
953 p_access_column_name2 => r_egl.information24,
954 p_time_entry_access_tab_nam2 => r_egl.information25,
955 p_time_entry_access_col_nam2 => r_egl.information26,
956
957 p_access_calc_rule => r_egl.information268,
958 p_allow_range_validation_flg => r_egl.information27,
959 p_user_defined_flag => r_egl.information28,
960 p_business_group_id => r_egl.information4,
961 p_legislation_code => r_egl.information29,
962 p_egl_attribute_category => r_egl.information110,
963 p_egl_attribute1 => r_egl.information111,
964 p_egl_attribute2 => r_egl.information112,
965 p_egl_attribute3 => r_egl.information113,
966 p_egl_attribute4 => r_egl.information114,
967 p_egl_attribute5 => r_egl.information115,
968 p_egl_attribute6 => r_egl.information116,
969 p_egl_attribute7 => r_egl.information117,
970 p_egl_attribute8 => r_egl.information118,
971 p_egl_attribute9 => r_egl.information119,
972 p_egl_attribute10 => r_egl.information120,
973 p_egl_attribute11 => r_egl.information121,
974 p_egl_attribute12 => r_egl.information122,
975 p_egl_attribute13 => r_egl.information123,
976 p_egl_attribute14 => r_egl.information124,
977 p_egl_attribute15 => r_egl.information125,
978 p_egl_attribute16 => r_egl.information126,
979 p_egl_attribute17 => r_egl.information127,
980 p_egl_attribute18 => r_egl.information128,
981 p_egl_attribute19 => r_egl.information129,
982 p_egl_attribute20 => r_egl.information130,
983 p_egl_attribute21 => r_egl.information131,
984 p_egl_attribute22 => r_egl.information132,
985 p_egl_attribute23 => r_egl.information133,
986 p_egl_attribute24 => r_egl.information134,
987 p_egl_attribute25 => r_egl.information135,
988 p_egl_attribute26 => r_egl.information136,
989 p_egl_attribute27 => r_egl.information137,
990 p_egl_attribute28 => r_egl.information138,
991 p_egl_attribute29 => r_egl.information139,
992 p_egl_attribute30 => r_egl.information140,
993 p_object_version_number => l_eligy_ovn,
994 p_effective_date => l_effective_date,
995 p_allow_range_validation_flag2 => r_egl.information30,
996 p_access_calc_rule2 => r_egl.information269,
997 p_time_access_calc_rule1 => r_egl.information270,
998 p_time_access_calc_rule2 => r_egl.information271
999 );
1000 elsif r_egl.dml_operation = 'CREATE' then
1001 hr_utility.set_location('going for create operation ',10);
1002
1003 ben_eligy_criteria_api.create_eligy_criteria(
1004 p_validate => FALSE,
1005 p_eligy_criteria_id => l_eligy_criteria_id,
1006 p_name => r_egl.information170,
1007 p_short_code => r_egl.information11,
1008 p_description => r_egl.information219,
1009 p_criteria_type => r_egl.information12,
1010 p_crit_col1_val_type_cd => r_egl.information13,
1011 p_crit_col1_datatype => r_egl.information14,
1012 p_col1_lookup_type => r_egl.information15,
1013 p_col1_value_set_id => r_egl.information266,
1014 p_access_table_name1 => r_egl.information16,
1015 p_access_column_name1 => r_egl.information17,
1016 p_time_entry_access_tab_nam1 => r_egl.information18,
1017 p_time_entry_access_col_nam1 => r_egl.information19,
1018
1019 p_crit_col2_val_type_cd => r_egl.information20,
1020 p_crit_col2_datatype => r_egl.information21,
1021 p_col2_lookup_type => r_egl.information22,
1022 p_col2_value_set_id => r_egl.information267,
1023 p_access_table_name2 => r_egl.information23,
1024 p_access_column_name2 => r_egl.information24,
1025 p_time_entry_access_tab_nam2 => r_egl.information25,
1026 p_time_entry_access_col_nam2 => r_egl.information26,
1027
1028 p_access_calc_rule => r_egl.information268,
1029 p_allow_range_validation_flg => r_egl.information27,
1030 p_user_defined_flag => r_egl.information28,
1031 p_business_group_id => r_egl.information4,
1032 p_legislation_code => r_egl.information29,
1033 p_egl_attribute_category => r_egl.information110,
1034 p_egl_attribute1 => r_egl.information111,
1035 p_egl_attribute2 => r_egl.information112,
1036 p_egl_attribute3 => r_egl.information113,
1037 p_egl_attribute4 => r_egl.information114,
1038 p_egl_attribute5 => r_egl.information115,
1039 p_egl_attribute6 => r_egl.information116,
1040 p_egl_attribute7 => r_egl.information117,
1041 p_egl_attribute8 => r_egl.information118,
1042 p_egl_attribute9 => r_egl.information119,
1043 p_egl_attribute10 => r_egl.information120,
1044 p_egl_attribute11 => r_egl.information121,
1045 p_egl_attribute12 => r_egl.information122,
1046 p_egl_attribute13 => r_egl.information123,
1047 p_egl_attribute14 => r_egl.information124,
1048 p_egl_attribute15 => r_egl.information125,
1049 p_egl_attribute16 => r_egl.information126,
1050 p_egl_attribute17 => r_egl.information127,
1051 p_egl_attribute18 => r_egl.information128,
1052 p_egl_attribute19 => r_egl.information129,
1053 p_egl_attribute20 => r_egl.information130,
1054 p_egl_attribute21 => r_egl.information131,
1055 p_egl_attribute22 => r_egl.information132,
1056 p_egl_attribute23 => r_egl.information133,
1057 p_egl_attribute24 => r_egl.information134,
1058 p_egl_attribute25 => r_egl.information135,
1059 p_egl_attribute26 => r_egl.information136,
1060 p_egl_attribute27 => r_egl.information137,
1061 p_egl_attribute28 => r_egl.information138,
1062 p_egl_attribute29 => r_egl.information139,
1063 p_egl_attribute30 => r_egl.information140,
1064 p_object_version_number => l_eligy_ovn,
1065 p_effective_date => l_effective_date,
1066 p_allow_range_validation_flag2 => r_egl.information30,
1067 p_access_calc_rule2 => r_egl.information269,
1068 p_time_access_calc_rule1 => r_egl.information270,
1069 p_time_access_calc_rule2 => r_egl.information271);
1070
1071 else
1072 hr_utility.set_location('invalid mode of operation passed',10);
1073 end if;
1074 p_eligy_criteria_id := l_eligy_criteria_id;
1075 end loop;
1076 exception
1077 when others then
1078 hr_utility.set_location('issues in writing criteria ',10);
1079 raise;
1080 end stage_to_criteria;
1081
1082
1083 FUNCTION check_criteria_rate_under_use(p_criteria_rate_defn_id NUMBER) RETURN varchar2 IS
1084 l_rate_matrix_rate_id number;
1085 l_rate_matrix_rate_id2 number;
1086 l_ret_vlaue varchar2(3);
1087 cursor c1 is select rate_matrix_rate_id from pqh_rate_matrix_rates_f
1088 where criteria_rate_defn_id = p_criteria_rate_defn_id;
1089 cursor c2 is select information1 from ben_copy_entity_results where
1090 information1 = p_criteria_rate_defn_id and table_alias = 'RCR';
1091 BEGIN
1092 open c1;
1093 fetch c1 into l_rate_matrix_rate_id;
1094 if c1%found then
1095 l_ret_vlaue := 'Yes';
1096 else
1097 open c2;
1098 fetch c2 into l_rate_matrix_rate_id2;
1099 if c2%found then
1100 l_ret_vlaue := 'Yes';
1101 else
1102 l_ret_vlaue := 'No';
1103 end if;
1104 close c2;
1105 end if;
1106 close c1;
1107 RETURN l_ret_vlaue;
1108 END check_criteria_rate_under_use;
1109
1110 procedure insert_rate_defn_tl(rateid in number,
1111 ratename in varchar2,
1112 lang in varchar2,
1113 slang in varchar2,
1114 cdate in date,
1115 cperson in number) is
1116 begin
1117
1118 insert into pqh_criteria_rate_defn_tl(CRITERIA_RATE_DEFN_ID,NAME,LANGUAGE,
1119 SOURCE_LANG,CREATION_DATE,CREATED_BY)
1120 values(rateid,ratename,lang,slang,cdate,cperson);
1121 commit;
1122
1123 end insert_rate_defn_tl;
1124
1125 procedure sync_rate_factors_tables(critId in varchar2,
1126 parentId in varchar2) is
1127 l_rate_factor_id pqh_rate_factor_on_elmnts.rate_factor_on_elmnt_id%TYPE;
1128 cursor c1 is select rate_factor_on_elmnt_id from
1129 Pqh_rate_factor_on_elmnts rf, Pqh_criteria_rate_elements re
1130 where rf.criteria_rate_element_id = re.criteria_rate_element_id
1131 and re.criteria_rate_defn_id = critId
1132 and rf.criteria_rate_factor_id = parentId;
1133 begin
1134 open c1;
1135 loop
1136 fetch c1 into l_rate_factor_id;
1137 delete from Pqh_rate_factor_on_elmnts
1138 where rate_factor_on_elmnt_id = l_rate_factor_id;
1139 exit when c1%NOTFOUND;
1140 end loop;
1141 close c1;
1142 end sync_rate_factors_tables;
1143
1144 FUNCTION is_used_in_matrix(p_selected_rate_matrix NUMBER, p_criteria_rate_defn_id NUMBER) RETURN varchar2 IS
1145 l_return_vlaue varchar2(1);
1146 BEGIN
1147 Select 'Y' INTO l_return_vlaue from
1148 pqh_rate_matrix_rates_f t1, pqh_rate_matrix_nodes t2
1149 Where t2.pl_id = p_selected_rate_matrix
1150 and t2.rate_matrix_node_id = t1.rate_matrix_node_id
1151 and sysdate between t1.effective_start_date and t1.effective_end_date
1152 and t1.criteria_rate_defn_id = p_criteria_rate_defn_id group by t1.criteria_rate_defn_id;
1153 RETURN l_return_vlaue;
1154 END is_used_in_matrix;
1155
1156 FUNCTION get_rate_factor_name(p_criteria_rate_factor_id NUMBER) RETURN varchar2 IS
1157 l_return_vlaue varchar2(30);
1158 BEGIN
1159 l_return_vlaue := 'RATE_FACTOR_NAME';
1160 RETURN l_return_vlaue;
1161 END get_rate_factor_name;
1162
1163 PROCEDURE is_crit_rate_short_name_uniq( sname in varchar2,
1164 rateId in number,
1165 bgId in number,
1166 isValid out nocopy varchar2)is
1167 l_name pqh_criteria_rate_defn_vl.short_name%TYPE;
1168 cursor c11 is
1169 SELECT SHORT_NAME FROM pqh_criteria_rate_defn_vl
1170 where upper(short_name) = upper(sname)
1171 and CRITERIA_RATE_DEFN_ID <> rateId
1172 and business_group_id = bgId;
1173 begin
1174 hr_utility.set_location('Rate Id'||to_char(rateId), 5);
1175 hr_utility.set_location('name'||sname, 10);
1176 open c11;
1177 fetch c11 into l_name;
1178 close c11;
1179
1180 if l_name is null then
1181 hr_utility.set_location('l_name'||l_name, 20);
1182 isValid := 'valid';
1183 else
1184 isValid := 'invalid';
1185 end if;
1186 end is_crit_rate_short_name_uniq;
1187
1188 PROCEDURE is_crit_rate_name_uniq(cname in varchar2,
1189 rateId in number,
1190 bgId in number,
1191 isValid out nocopy varchar2)is
1192 l_name pqh_criteria_rate_defn_vl.name%TYPE;
1193 cursor c11 is
1194 SELECT NAME FROM pqh_criteria_rate_defn_vl
1195 where upper(name) = upper(cname)
1196 and CRITERIA_RATE_DEFN_ID <> rateId
1197 and business_group_id = bgId;
1198 begin
1199 open c11;
1200 fetch c11 into l_name;
1201 close c11;
1202 if l_name is null then
1203 isValid := 'valid';
1204 else
1205 isValid := 'invalid';
1206 end if;
1207 end is_crit_rate_name_uniq;
1208
1209 PROCEDURE cascade_rate_factors_table(rateTypeId varchar2) IS
1210 BEGIN
1211 delete from Pqh_criteria_rate_factors
1212 where criteria_rate_defn_id = rateTypeId;
1213 END cascade_rate_factors_table;
1214
1215 --
1216 -- Procedures needed for adding or removing criteria rate definition from Rate Matrix.
1217 --
1218 Procedure remove_crd_from_rate_matrix
1219 (p_business_group_id in number,
1220 p_criteria_rate_defn_id in number,
1221 p_copy_entity_txn_id in number,
1222 p_removed_crd_name out nocopy varchar2,
1223 p_removed_dep_crd out nocopy varchar2) is
1224 --
1225 Cursor csr_exist_crd(p_crd_id in number) is
1226 Select 'x'
1227 from ben_copy_entity_results
1228 Where copy_entity_txn_id = p_copy_entity_txn_id
1229 AND table_alias = 'RMR'
1230 AND information162 = p_crd_id
1231 and dml_operation <> 'DELETE'
1232 and information1 is not null;
1233 --
1234 Cursor csr_rm is
1235 Select information1
1236 from ben_copy_entity_results
1237 Where copy_entity_txn_id = p_copy_entity_txn_id
1238 AND table_alias = 'PLN';
1239 --
1240 Cursor csr_ref_crd(p_rate_matrix_id in number) is
1241 Select copy_entity_result_id, information1 , nvl(information5,'name') crd_name,information160
1242 from ben_copy_entity_results
1243 Where table_alias = 'RCR'
1244 and copy_entity_txn_id = p_copy_entity_txn_id
1245 and (information1 = p_criteria_rate_defn_id OR
1246 information1 in (select criteria_rate_defn_id
1247 from pqh_criteria_rate_factors
1248 Where parent_criteria_rate_defn_id = p_criteria_rate_defn_id
1249 and (parent_rate_matrix_id is null or parent_rate_matrix_id = p_rate_matrix_id)
1250 )
1251 );
1252 --
1253 l_dummy varchar2(1);
1254 l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%type;
1255 l_criteria_rate_defn_id ben_copy_entity_results.information1%type;
1256 l_criteria_rate_defn_name ben_copy_entity_results.information5%type;
1257 l_curr_order_number ben_copy_entity_results.information160%type;
1258 l_curr_rate_matrix_id ben_copy_entity_results.information1%type;
1259 --
1260 Begin
1261 --
1262 hr_utility.set_location('Entering',5);
1263 l_criteria_rate_defn_name := null;
1264 p_removed_crd_name := null;
1265 l_curr_rate_matrix_id := null;
1266
1267 Open csr_rm;
1268 Fetch csr_rm into l_curr_rate_matrix_id;
1269 Close csr_rm;
1270 --
1271 If l_curr_rate_matrix_id is null then
1272 l_curr_rate_matrix_id := -1;
1273 End if;
1274 --
1275 For del_crd_row in csr_ref_crd(l_curr_rate_matrix_id) loop
1276 --
1277 l_criteria_rate_defn_id := del_crd_row.information1;
1278 l_copy_entity_result_id := del_crd_row.copy_entity_result_id;
1279 l_curr_order_number := del_crd_row.information160;
1280 If l_criteria_rate_defn_id = p_criteria_rate_defn_id then
1281 --
1282 If p_removed_crd_name is null then
1283 p_removed_crd_name := del_crd_row.crd_name;
1284 else
1285 p_removed_crd_name := p_removed_crd_name ||','||del_crd_row.crd_name;
1286 End if;
1287 Else
1288 --
1289 If p_removed_dep_crd is null then
1290 p_removed_dep_crd := del_crd_row.crd_name;
1291 else
1292 p_removed_dep_crd := p_removed_dep_crd ||','||del_crd_row.crd_name;
1293 End if;
1294 --
1295 End if;
1296
1297 hr_utility.set_location('Removing:'||p_removed_crd_name,5);
1298 --
1299 -- 1) set the RMR rows for this removed criteria rate definition to deleted.
1300 -- Check if this criteria rate defintion was added as part of this transaction.
1301 -- If rates for rate matrix nodes exist for this criteria rate definition in the
1302 -- master table, then RMR rows will have a rate_matrix_rate_id
1303 --
1304 Open csr_exist_crd(l_criteria_rate_defn_id);
1305 Fetch csr_exist_crd into l_dummy;
1306 If csr_exist_crd%notfound then
1307 --
1308 -- Criteria Rate definition was added to rate matrix as a part of current txn.
1309 --
1310 Delete from ben_copy_entity_results
1311 Where copy_entity_txn_id = p_copy_entity_txn_id
1312 AND table_alias = 'RMR'
1313 AND information162 = l_criteria_rate_defn_id
1314 and dml_operation <> 'DELETE';
1315
1316 Else
1317 --
1318 -- Criteria Rate definition was previously added to rate matrix.Hence rates
1319 -- must be deleted from the master table. Hence mark it for deletion in
1320 -- staging area.
1321 --
1322 Update ben_copy_entity_results
1323 set dml_operation = 'DELETE'
1324 Where copy_entity_txn_id = p_copy_entity_txn_id
1325 AND table_alias = 'RMR'
1326 AND information162 = l_criteria_rate_defn_id
1327 and dml_operation <> 'DELETE';
1328 --
1329 End if;
1330 Close csr_exist_crd;
1331 --
1332 -- Delete RCR row.
1333 --
1334 hr_utility.set_location('Deleting:'||to_char(l_copy_entity_result_id),15);
1335 Delete from ben_copy_entity_results
1336 Where copy_entity_txn_id = p_copy_entity_txn_id
1337 and table_alias = 'RCR'
1338 and copy_entity_result_id = l_copy_entity_result_id;
1339 --
1340 -- Adjust order number for remanining RCR rows.
1341 --
1342 /**
1343 Update ben_copy_entity_results
1344 set information160 = (information160 - 1)
1345 Where copy_entity_txn_id = p_copy_entity_txn_id
1346 and table_alias = 'RCR'
1347 and information160 > l_curr_order_number;
1348 **/
1349 --
1350 End loop;
1351 --
1352 End;
1353 --
1354 Procedure rebuild_rbr_rows
1355 (p_business_group_id in number,
1356 p_copy_entity_txn_id in number
1357 ) is
1358 --
1359 --
1360 l_dummy varchar2(1);
1361 l_copy_entity_result_id ben_copy_entity_results.copy_entity_result_id%type;
1362 l_criteria_rate_defn_id ben_copy_entity_results.information1%type;
1363 l_criteria_rate_defn_name ben_copy_entity_results.information5%type;
1364 l_curr_order_number ben_copy_entity_results.information160%type;
1365 l_new_order_number ben_copy_entity_results.information160%type := 0;
1366 l_parent pqh_criteria_rate_defn.criteria_rate_defn_id%type;
1367 l_curr_rate_matrix_id ben_pl_f.pl_id%type;
1368 --
1369 Cursor csr_ref_crd is
1370 Select copy_entity_result_id, information1 , nvl(information5,'name') crd_name,information160
1371 from ben_copy_entity_results
1372 Where table_alias = 'RCR'
1373 and copy_entity_txn_id = p_copy_entity_txn_id
1374 order by information160;
1375 --
1376 Cursor csr_rm is
1377 Select information1
1378 from ben_copy_entity_results
1379 Where copy_entity_txn_id = p_copy_entity_txn_id
1380 AND table_alias = 'PLN';
1381 --
1382 Cursor csr_find_parent(p_rate_matrix_id in number, p_criteria_rate_defn_id in number) is
1383 select a.parent_criteria_rate_defn_id parent_id, b.name parent_name
1384 from pqh_criteria_rate_factors a, pqh_criteria_rate_defn_vl b
1385 Where a.criteria_rate_defn_id = p_criteria_rate_defn_id
1386 and (parent_rate_matrix_id is null or parent_rate_matrix_id = p_rate_matrix_id)
1387 and a.parent_criteria_rate_defn_id = b.criteria_rate_defn_id;
1388 --
1389 Cursor csr_is_parent_added(p_parent in number) is
1390 Select 'x'
1391 from ben_copy_entity_results
1392 Where table_alias = 'RCR'
1393 and copy_entity_txn_id = p_copy_entity_txn_id
1394 and information1 = p_parent;
1395 --
1396 Begin
1397 --
1398 hr_utility.set_location('Entering rebuild_rbr_rows',5);
1399 --
1400 /**
1401 l_curr_rate_matrix_id := null;
1402
1403 Open csr_rm;
1404 Fetch csr_rm into l_curr_rate_matrix_id;
1405 Close csr_rm;
1406 --
1407 If l_curr_rate_matrix_id is null then
1408 l_curr_rate_matrix_id := -1;
1409 hr_utility.set_location('null rate matrix',5);
1410 End if;
1411 --
1412 --
1413 -- Validate if any criteria_rate_defn is dependent on another and if so, if the parent has
1414 -- been added to the rate matrix.
1415 -- Find all criteria rate defn added to rate matrix
1416 --
1417 hr_multi_message.enable_message_list;
1418 For added_rcr_rec in csr_ref_crd loop
1419 --
1420 l_criteria_rate_defn_id := null;
1421 l_criteria_rate_defn_name := null;
1422 l_parent:= null;
1423 --
1424 l_criteria_rate_defn_id := added_rcr_rec.information1;
1425 --
1426 hr_utility.set_location('crd ='||to_char(l_criteria_rate_defn_id),5);
1427 -- Find its parent
1428 --
1429 For parent_rec in csr_find_parent(l_curr_rate_matrix_id,l_criteria_rate_defn_id) loop
1430 l_parent:= parent_rec.parent_id;
1431 l_criteria_rate_defn_name := parent_rec.parent_name;
1432 -- Is parent added
1433 hr_utility.set_location('parent id ='||to_char(l_parent),5);
1434 hr_utility.set_location('parent name ='||l_criteria_rate_defn_name,5);
1435 Open csr_is_parent_added(l_parent);
1436 Fetch csr_is_parent_added into l_dummy;
1437 If csr_is_parent_added%notfound then
1438 hr_utility.set_location('parent not found',5);
1439 hr_utility.set_message(8302,'PQH_RBC_REENTER_PLAN_INFO');
1440 hr_multi_message.add;
1441 End if;
1442 Close csr_is_parent_added;
1443
1444 End loop;
1445 End loop;
1446 **/
1447 --
1448 -- Adjust order number for remanining RCR rows.
1449 --
1450 l_criteria_rate_defn_id := null;
1451 l_criteria_rate_defn_name := null;
1452 For del_crd_row in csr_ref_crd loop
1453 --
1454 l_criteria_rate_defn_id := del_crd_row.information1;
1455 l_copy_entity_result_id := del_crd_row.copy_entity_result_id;
1456 l_curr_order_number := del_crd_row.information160;
1457 --
1458 --
1459 Update ben_copy_entity_results
1460 set information160 = l_new_order_number
1461 Where copy_entity_txn_id = p_copy_entity_txn_id
1462 and table_alias = 'RCR'
1463 and copy_entity_result_id = l_copy_entity_result_id;
1464 l_new_order_number := l_new_order_number+1;
1465 --
1466 End loop;
1467 --
1468 End;
1469 --
1470 Procedure add_crd_to_rate_matrix
1471 (p_business_group_id in number,
1472 p_criteria_rate_defn_id in number,
1473 p_copy_entity_txn_id in number,
1474 p_define_min_flag in varchar2,
1475 p_define_mid_flag in varchar2,
1476 p_define_max_flag in varchar2,
1477 p_define_std_flag in varchar2,
1478 p_currency_code in varchar2,
1479 p_uom in varchar2,
1480 p_rate_calc_cd in varchar2,
1481 p_display_computed_values in varchar2,
1482 p_name in varchar2
1483 ) is
1484 --
1485 l_rcr_cer_id number;
1486 l_rcr_cer_ovn number;
1487 l_order_num number := null;
1488 --
1489 l_rcr_tr_id number;
1490 l_rcr_tr_name varchar2(150);
1491 --
1492 Cursor csr_next_order_num is
1493 Select max(information160) + 1
1494 from ben_copy_entity_results
1495 Where copy_entity_txn_id = p_copy_entity_txn_id
1496 and table_alias = 'RCR';
1497 --
1498 Begin
1499 --
1500 Open csr_next_order_num;
1501 Fetch csr_next_order_num into l_order_num;
1502 Close csr_next_order_num;
1503 --
1504 If l_order_num is null then
1505 l_order_num := 0;
1506 End if;
1507 --
1508 pqh_gsp_hr_to_stage.get_table_route_details
1509 (p_table_alias => 'RCR',
1510 p_table_route_id => l_rcr_tr_id,
1511 p_table_name => l_rcr_tr_name);
1512 --
1513 -- When a new criteria rate definition is added, Add a RCR row with the correct
1514 -- order number and details
1515 ben_copy_entity_results_api.create_copy_entity_results(
1516 p_effective_date => trunc(sysdate)
1517 ,p_copy_entity_txn_id => p_copy_entity_txn_id
1518 ,p_result_type_cd => 'DISPLAY'
1519 ,p_table_name => l_rcr_tr_name
1520 ,p_table_route_id => l_rcr_tr_id
1521 ,p_table_alias => 'RCR'
1522 ,p_dml_operation => 'COPIED'
1523 ,p_information1 => p_criteria_rate_defn_id
1524 ,p_information4 => p_business_group_id
1525 ,p_information5 => p_name
1526 ,p_information49 => p_uom
1527 ,p_information50 => p_currency_code
1528 ,p_information160 => l_order_num
1529 ,p_information111 => p_define_min_flag
1530 ,p_information112 => p_define_mid_flag
1531 ,p_information113 => p_define_max_flag
1532 ,p_information114 => p_define_std_flag
1533 ,p_information115 => p_rate_calc_cd
1534 ,p_information116 => p_display_computed_values
1535 ,p_copy_entity_result_id => l_rcr_cer_id
1536 ,p_object_version_number => l_rcr_cer_ovn);
1537 --
1538 End;
1539 --
1540 --
1541
1542
1543
1544
1545
1546 function allow_hgrid_add(p_copy_entity_txn_id in number,p_max_allowed in number) return varchar2 IS
1547 l_max number;
1548 BEGIN
1549 if future_criteria_exist(p_copy_entity_txn_id) = 'YES' then
1550 return 'DisallowAdd';
1551 end if;
1552
1553 select max(nvl(information160,1))into l_max from ben_copy_entity_results where copy_entity_txn_id = p_copy_entity_txn_id
1554 and table_alias in('RBC_CRIT', 'PLN') and dml_operation <> 'DELETE';
1555 if l_max < p_max_allowed then
1556 RETURN 'AllowAdd';
1557 else
1558 RETURN 'DisallowAdd';
1559 end if;
1560 END allow_hgrid_add;
1561 --
1562 --
1563 procedure delete_rate_values(p_copy_entity_txn_id in number,
1564 p_copy_entity_result_id in number
1565 ) is
1566 l_copy_entity_result_id number;
1567 l_rate_matrix_node_id number;
1568 l_level_number number;
1569 -- p_mode in UPDATE or CREATE
1570 cursor c1 is select copy_entity_result_id, information160
1571 from ben_copy_entity_results
1572 where copy_entity_txn_id = p_copy_entity_txn_id
1573 and gs_parent_entity_result_id = p_copy_entity_result_id
1574 and table_alias in ('RMN','RMV');
1575 --Bug#9206953 vkodedal
1576 cursor c2 is select information1,copy_entity_result_id
1577 from ben_copy_entity_results
1578 where copy_entity_txn_id = p_copy_entity_txn_id
1579 and (copy_entity_result_id = l_copy_entity_result_id
1580 or gs_parent_entity_result_id = l_copy_entity_result_id)
1581 and table_alias in ('RMN','RMV');
1582
1583 begin
1584 open c1;
1585 loop
1586 fetch c1 into l_copy_entity_result_id, l_level_number;
1587 EXIT WHEN c1%NOTFOUND;
1588 if(is_lowest_level(p_copy_entity_txn_id,
1589 l_copy_entity_result_id,
1590 l_level_number) = 'N')then
1591 delete_rate_values(p_copy_entity_txn_id, l_copy_entity_result_id);
1592 end if;
1593
1594 delete from ben_copy_entity_results
1595 where INFORMATION1 = l_copy_entity_result_id
1596 and copy_entity_txn_id = p_copy_entity_txn_id
1597 and table_alias = 'RBR';
1598
1599 open c2;
1600 loop
1601 --Bug#9206953 vkodedal
1602 fetch c2 into l_rate_matrix_node_id,l_copy_entity_result_id;
1603 EXIT WHEN c2%NOTFOUND;
1604 delete from ben_copy_entity_results
1605 where INFORMATION1 = l_rate_matrix_node_id
1606 and copy_entity_txn_id = p_copy_entity_txn_id
1607 and copy_entity_result_id=l_copy_entity_result_id
1608 and table_alias in ('RMV','RMN')
1609 and dml_operation = 'CREATE';
1610
1611 update ben_copy_entity_results
1612 set dml_operation = 'DELETE',datetrack_mode = 'DELETE'
1613 where INFORMATION1 = l_rate_matrix_node_id
1614 and copy_entity_txn_id = p_copy_entity_txn_id
1615 and copy_entity_result_id=l_copy_entity_result_id
1616 and table_alias in ('RMV','RMN')
1617 and dml_operation in ('UPDATE','COPIED');
1618
1619 end loop;
1620 close c2;
1621 end loop;
1622 close c1;
1623 delete from ben_copy_entity_results
1624 where INFORMATION1 = p_copy_entity_result_id
1625 and copy_entity_txn_id = p_copy_entity_txn_id
1626 and table_alias = 'RBR';
1627
1628 delete from ben_copy_entity_results
1629 where copy_entity_result_id = p_copy_entity_result_id
1630 and copy_entity_txn_id = p_copy_entity_txn_id
1631 and table_alias in ('RMV','RMN')
1632 and dml_operation = 'CREATE';
1633
1634 update ben_copy_entity_results
1635 set dml_operation = 'DELETE',datetrack_mode = 'DELETE'
1636 where copy_entity_result_id = p_copy_entity_result_id
1637 and copy_entity_txn_id = p_copy_entity_txn_id
1638 and table_alias in ('RMV','RMN')
1639 and dml_operation in ('UPDATE','COPIED');
1640
1641 end;
1642 --
1643 function is_lowest_level(p_copy_entity_txn_id number,
1644 p_copy_entity_result_id number,
1645 p_level_number number) return varchar2 is
1646 l_max_level_number number;
1647 begin
1648 select max(information160) into l_max_level_number from ben_copy_entity_results
1649 where copy_entity_txn_id = p_copy_entity_txn_id
1650 and table_alias = 'RBC_CRIT' and dml_operation <> 'DELETE';
1651
1652 if(p_level_number = 1 or p_level_number < l_max_level_number) then
1653 return 'N';
1654 else
1655 return 'Y';
1656 end if;
1657 end is_lowest_level;
1658
1659 --
1660 --
1661 procedure cancel_rate_matrix_txn(p_copy_entity_txn_id in number,p_status out nocopy varchar2) is
1662
1663 l_copy_entity_result_id number;
1664 cursor c1 is select copy_entity_result_id from ben_copy_entity_results
1665 where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'PLN';
1666
1667 begin
1668 FND_MSG_PUB.initialize;
1669
1670 open c1;
1671 fetch c1 into l_copy_entity_result_id;
1672 close c1;
1673 if l_copy_entity_result_id is null then
1674 p_status := 'NO';
1675 else
1676 p_status := 'YES';
1677 end if;
1678
1679
1680
1681 hr_utility.set_location('going for deleting entire rate matrix txn',100);
1682 delete from ben_copy_entity_results where copy_entity_txn_id = p_copy_entity_txn_id;
1683 delete from pqh_copy_entity_txns where copy_entity_txn_id = p_copy_entity_txn_id;
1684 hr_utility.set_location('deleting entire rate matrix txn done',110);
1685 exception
1686 when others then
1687 hr_utility.set_location('issues in deleting matrix txn',100);
1688 raise;
1689 end cancel_rate_matrix_txn;
1690
1691 procedure rate_columns_in_sync(critId in number,
1692 pMaxFlag in varchar2,
1693 pMinFlag in varchar2,
1694 pMidFlag in varchar2,
1695 pDflFlag in varchar2,
1696 pOutValue out nocopy varchar2) is
1697 l_max_flag varchar(2);
1698 l_mid_flag varchar(2);
1699 l_min_flag varchar(2);
1700 l_dfl_flag varchar(2);
1701 begin
1702 select DEFINE_MAX_RATE_FLAG, DEFINE_MIN_RATE_FLAG, DEFINE_MID_RATE_FLAG, DEFINE_STD_RATE_FLAG
1703 into l_max_flag, l_min_flag, l_mid_flag, l_dfl_flag
1704 from pqh_criteria_rate_defn
1705 where CRITERIA_RATE_DEFN_ID = critId;
1706 if( (pMaxFlag = 'Y' AND l_max_flag <> pMaxFlag) OR
1707 (pMidFlag = 'Y' AND l_mid_flag <> pMidFlag) OR
1708 (pMinFlag = 'Y' AND l_min_flag <> pMinFlag) OR
1709 (pDflFlag = 'Y' AND l_dfl_flag <> pDflFlag)) then
1710 pOutValue := 'NO';
1711 else
1712 pOutValue := 'YES';
1713 end if;
1714 end rate_columns_in_sync;
1715
1716 FUNCTION get_currency_name(p_currency_code varchar2) RETURN varchar2 IS
1717 l_currency_name varchar2(80);
1718 cursor c1 is select name from fnd_currencies_vl
1719 where currency_code = p_currency_code;
1720 BEGIN
1721 open c1;
1722 loop
1723 fetch c1 into l_currency_name;
1724 exit when c1%notfound;
1725 end loop;
1726 close c1;
1727
1728 RETURN l_currency_name;
1729 END get_currency_name;
1730
1731 FUNCTION get_formula_name(p_formula_id varchar2) RETURN varchar2 IS
1732 l_formula_id varchar2(80);
1733 cursor c1 is select formula_name from ff_formulas_f
1734 where formula_id = p_formula_id;
1735 BEGIN
1736 open c1;
1737 loop
1738 fetch c1 into l_formula_id;
1739 exit when c1%notfound;
1740 end loop;
1741 close c1;
1742
1743 RETURN l_formula_id;
1744 END get_formula_name;
1745
1746 --
1747 -- Function to return the datatype of the value returned by a valueset
1748 -- Char - C / V
1749 -- Number - N
1750 -- Standard Date - X / D
1751 -- Treat any other value as invalid datatype
1752 --
1753 Function get_vset_datatype(p_value_set_id in number) return varchar2 is
1754 --
1755 -- Format type maybe 'C' , 'N' or 'X'
1756 --
1757 Cursor csr_val_type is
1758 Select validation_type, format_type
1759 from fnd_flex_value_sets
1760 Where flex_value_set_id = p_value_set_id;
1761 --
1762 -- The column type may be 'C' , 'V', 'N' , 'D' or null
1763 --
1764 Cursor csr_id_col_type is
1765 select nvl(id_column_type,'O')
1766 from fnd_flex_validation_tables
1767 where flex_value_set_id = p_value_set_id;
1768 --
1769 l_validation_type fnd_flex_value_sets.validation_type%type;
1770 l_format_type fnd_flex_value_sets.format_type%type;
1771 l_col_type fnd_flex_validation_tables.id_column_type%type;
1772 --
1773 Begin
1774 -- Get the validation type of the valueset
1775 open csr_val_type;
1776 Fetch csr_val_type into l_validation_type, l_format_type;
1777 If csr_val_type%notfound then
1778 -- Invalid value set id passed.
1779 Close csr_val_type;
1780 return 'O';
1781 Else
1782 --
1783 -- Valid value set
1784 --
1785 Close csr_val_type;
1786 If l_validation_type = 'F' then
1787 -- Table type valueset
1788 open csr_id_col_type;
1789 Fetch csr_id_col_type into l_col_type;
1790 If csr_id_col_type%notfound then
1791 Close csr_id_col_type;
1792 return 'O';
1793 Else
1794 Close csr_id_col_type;
1795 return l_col_type;
1796 End if;
1797 --
1798 Else
1799 return l_format_type;
1800 End if;
1801 --
1802 End if;
1803 --
1804 End get_vset_datatype;
1805 --
1806 --
1807 end pqh_rbc_utility;