[Home] [Help]
PACKAGE BODY: APPS.PQH_RBC_STAGE
Source
1 PACKAGE BODY PQH_RBC_STAGE AS
2 /* $Header: pqrbcstg.pkb 120.7 2006/03/14 09:26 srajakum noship $ */
3 procedure set_session_date(p_effective_date in date) is
4 l_cnt number;
5 begin
6 select 1
7 into l_cnt
8 from fnd_sessions
9 where session_id = userenv('sessionid');
10 exception
11 when no_data_found then
12 hr_utility.set_location('session date not there, inserting',10);
13 insert into fnd_sessions (session_id,effective_date) values (userenv('sessionid'),trunc(p_effective_date));
14 when others then
15 hr_utility.set_location('issues in session date pulling ',10);
16 raise;
17 end;
18 procedure get_criteria(p_criteria_short_code in varchar2,
19 p_business_group_id in number,
20 p_crit_rec out nocopy ben_eligy_criteria%rowtype) is
21 begin
22 select *
23 into p_crit_rec
24 from ben_eligy_criteria
25 where short_code =p_criteria_short_code
26 and business_group_id = p_business_group_id;
27 exception
28 when no_data_found then
29 hr_utility.set_location('no bg specific row exists for short_code'||p_criteria_short_code,25);
30 begin
31 select *
32 into p_crit_rec
33 from ben_eligy_criteria
34 where short_code =p_criteria_short_code
35 and business_group_id is null;
36 exception
37 when no_data_found then
38 hr_utility.set_location('no global row for short_code'||p_criteria_short_code,25);
39 raise;
40 when others then
41 hr_utility.set_location('issues in glb short_code'||p_criteria_short_code,25);
42 raise;
43 end;
44 when others then
45 hr_utility.set_location('issues in getting criteria for short_code'||p_criteria_short_code,25);
46 raise;
47 end;
48 function get_rmn_cer(p_rmn_id in number,
49 p_cet_id in number) return number is
50 l_rmn_cer_id number;
51 begin
52 select copy_entity_result_id
53 into l_rmn_cer_id
54 from ben_copy_entity_results
55 where copy_entity_txn_id = p_cet_id
56 and table_alias ='RMN'
57 and information1 = p_rmn_id;
58 hr_utility.set_location('RMN cer'||l_rmn_cer_id,20);
59 return l_rmn_cer_id;
60 exception
61 when others then
62 hr_utility.set_location('issues in getting RMN cer'||p_rmn_id,10);
63 raise;
64 end;
65 function get_ph_name(p_pos_hier_ver_id in number) return varchar2 is
66 l_ph_name varchar2(240);
67 begin
68 select ph.name
69 into l_ph_name
70 from per_position_structures ph, per_pos_structure_versions phv
71 where ph.position_structure_id = phv.position_structure_id
72 and phv.pos_structure_version_id = p_pos_hier_ver_id;
73 return l_ph_name;
74 exception
75 when others then
76 hr_utility.set_location('issues in pulling ph name'||p_pos_hier_ver_id,10);
77 raise;
78 end;
79 function get_oh_name(p_org_hier_ver_id in number) return varchar2 is
80 l_oh_name varchar2(240);
81 begin
82 select oh.name
83 into l_oh_name
84 from per_organization_structures oh, per_org_structure_versions ohv
85 where oh.organization_structure_id = ohv.organization_structure_id
86 and ohv.org_structure_version_id = p_org_hier_ver_id;
87 return l_oh_name;
88 exception
89 when others then
90 hr_utility.set_location('issues in pulling oh name'||p_org_hier_ver_id,10);
91 raise;
92 end;
93 function get_crit_type(p_criteria_short_code in varchar2,
94 p_business_group_id in number) return varchar2 is
95 l_crit ben_eligy_criteria%rowtype;
96 begin
97 get_criteria(p_criteria_short_code => p_criteria_short_code,
98 p_business_group_id => p_business_group_id,
99 p_crit_rec => l_crit);
100 if l_crit.crit_col1_val_type_cd ='ORG_HIER' then
101 return 'OH';
102 elsif l_crit.crit_col1_val_type_cd = 'POS_HIER' then
103 return 'PH';
104 else
105 return 'OTHER';
106 end if;
107 end get_crit_type;
108 function node_value(p_val_type_cd in varchar2,
109 p_lookup_type in varchar2,
110 p_crit_col_dtyp in varchar2,
111 p_value_set_id in number,
112 p_range_flag in varchar2,
113 p_number_value1 in number,
114 p_number_value2 in number,
115 p_char_value1 in varchar2,
116 p_char_value2 in varchar2,
117 p_date_value1 in date,
118 p_date_value2 in date,
119 p_effective_date in date) return varchar2 is
120 l_oh_desc varchar2(2000);
121 l_org_desc varchar2(2000);
122 l_ph_desc varchar2(2000);
123 l_pos_desc varchar2(2000);
124 l_node_desc varchar2(2000);
125 l_node1_desc varchar2(2000);
126 l_node2_desc varchar2(2000);
127 begin
128 if p_val_type_cd ='LOOKUP' then
129 hr_utility.set_location('based on lkp',10);
130 hr_utility.set_location('lkp_type'||p_lookup_type,40);
131 hr_utility.set_location('lkp_code'||p_char_value1,40);
132 if p_char_value1 is not null then
133 l_node1_desc := hr_general.decode_lookup(p_lookup_type => p_lookup_type,
134 p_lookup_code => p_char_value1);
135 else
136 l_node1_desc := hr_general.decode_lookup(p_lookup_type => p_lookup_type,
137 p_lookup_code => p_number_value1);
138 end if;
139 if p_range_flag = 'Y' then
140 if p_char_value2 is not null then
141 l_node2_desc := hr_general.decode_lookup(p_lookup_type => p_lookup_type,
142 p_lookup_code => p_char_value2);
143 else
144 l_node2_desc := hr_general.decode_lookup(p_lookup_type => p_lookup_type,
145 p_lookup_code => p_number_value2);
146 end if;
147 l_node_desc := l_node1_desc ||' - '||l_node2_desc;
148 else
149 l_node_desc := l_node1_desc ;
150 end if;
151 elsif p_val_type_cd ='ORG_HIER' then
152 hr_utility.set_location('based on oh'||p_number_value1||' : '||p_number_value2,40);
153 l_oh_desc := get_oh_name(p_number_value1);
154 l_org_desc := hr_general.decode_organization(p_organization_id => p_number_value2);
155 l_node_desc := l_oh_desc ||' - '||l_org_desc;
156 elsif p_val_type_cd ='POS_HIER' then
157 hr_utility.set_location('based on ph'||p_number_value1||' : '||p_number_value2,40);
158 l_ph_desc := get_ph_name(p_number_value1);
159 l_pos_desc := hr_general.decode_position(p_position_id => p_number_value2);
160 l_node_desc := l_ph_desc ||' - '||l_pos_desc;
161 elsif p_val_type_cd ='VAL_SET' then
162 hr_utility.set_location('based on vset'||p_value_set_id,40);
163 if p_crit_col_dtyp ='C' then
164 hr_utility.set_location('char based vset',40);
165 l_node1_desc := pqh_utility.get_display_value(p_value => p_char_value1,
166 p_value_set_id => p_value_set_id);
167 hr_utility.set_location('val1 is '||l_node1_desc,40);
168 if p_range_flag = 'Y' and p_char_value2 is not null then
169 l_node2_desc := pqh_utility.get_display_value(p_value => p_char_value2,
170 p_value_set_id => p_value_set_id);
171 hr_utility.set_location('val2 is '||l_node2_desc,40);
172 l_node_desc := l_node1_desc ||' - '||l_node2_desc;
173 else
174 l_node_desc := l_node1_desc ;
175 end if;
176 elsif p_crit_col_dtyp ='N' then
177 hr_utility.set_location('num based vset',40);
178 l_node1_desc := pqh_utility.get_display_value(p_value => to_char(p_number_value1),
179 p_value_set_id => p_value_set_id);
180 hr_utility.set_location('val1 is '||l_node1_desc,40);
181 if p_range_flag = 'Y' and p_number_value2 is not null then
182 l_node2_desc := pqh_utility.get_display_value(p_value => p_number_value2,
183 p_value_set_id => p_value_set_id);
184 hr_utility.set_location('val2 is '||l_node2_desc,40);
185 l_node_desc := l_node1_desc ||' - '||l_node2_desc;
186 else
187 l_node_desc := l_node1_desc ;
188 end if;
189 elsif p_crit_col_dtyp ='D' then
190 hr_utility.set_location('Date based vset',40);
191 select to_char(p_date_value1,fnd_profile.value('ICX_DATE_FORMAT_MASK'))
192 into l_node1_desc
193 from dual;
194 hr_utility.set_location('val1 is '||l_node1_desc,40);
195 if p_range_flag = 'Y' and p_date_value2 is not null then
196 select to_char(p_date_value2,fnd_profile.value('ICX_DATE_FORMAT_MASK'))
197 into l_node2_desc
198 from dual;
199 hr_utility.set_location('val2 is '||l_node2_desc,40);
200 l_node_desc := l_node1_desc ||' - '||l_node2_desc;
201 else
202 l_node_desc := l_node1_desc ;
203 end if;
204 end if;
205 end if;
206 return l_node_desc;
207 end node_value;
208 function build_rate_node_name(p_rate_matrix_node_id in number,
209 p_business_group_id in number,
210 p_node_short_code in varchar2 default null,
211 p_effective_date in date) return varchar2 is
212 l_crit_short_code varchar2(80);
213 l_crt_rec ben_eligy_criteria%rowtype;
214 l_node1_desc varchar2(2000);
215 l_node2_desc varchar2(2000);
216 l_node_sum_desc varchar2(2000);
217 l_node_name varchar2(2000);
218 l_part2 varchar2(30);
219 l_valuset_val_type varchar2(30);
220 l_valuset_sql_stmt varchar2(5000);
221 l_valuset_err_st varchar2(60);
222 l_val_set_name varchar2(1000);
223 l_dep_val_set varchar2(1) := 'Y';
224 l_prnt_value1 varchar2(1000);
225 l_prnt_value2 varchar2(1000);
226 l_value3 varchar2(1000);
227 l_value4 varchar2(1000);
228 cursor c_rnv is select * from pqh_rt_matrix_node_values
229 where rate_matrix_node_id = p_rate_matrix_node_id;
230 begin
231 -- get the node details
232 hr_utility.set_location('get node value for '||p_rate_matrix_node_id,10);
233 pqh_utility.init_query_date;
234 pqh_utility.set_query_date(p_effective_date => p_effective_date);
235 if p_node_short_code is null then
236 begin
237 select criteria_short_code
238 into l_crit_short_code
239 from pqh_rate_matrix_nodes
240 where rate_matrix_node_id = p_rate_matrix_node_id;
241 hr_utility.set_location('nodeshort_code is'||l_crit_short_code,10);
242 exception
243 when others then
244 hr_utility.set_location('issues in getting '||l_crit_short_code,10);
245 raise;
246 end;
247 else
248 l_crit_short_code := p_node_short_code;
249 end if;
250 if l_crit_short_code is not null then
251 hr_utility.set_location('get criteria detail'||l_crit_short_code,25);
252 get_criteria(p_criteria_short_code => l_crit_short_code,
253 p_business_group_id => p_business_group_id,
254 p_crit_rec => l_crt_rec);
255 hr_utility.set_location('criteria desc is'||substr(l_crt_rec.name,1,30),30);
256 if l_crt_rec.crit_col2_val_type_cd is not null then
257 hr_utility.set_location('2 part critera',30);
258 l_part2 := 'Y';
259 end if;
260 for i in c_rnv loop
261 l_node1_desc := '';
262 hr_utility.set_location('node_value_id is'||i.node_value_id,40);
263 hr_utility.set_location('node_ dt is'||l_crt_rec.crit_col1_datatype,40);
264 l_node1_desc := node_value(p_val_type_cd => l_crt_rec.crit_col1_val_type_cd,
265 p_lookup_type => l_crt_rec.col1_lookup_type,
266 p_crit_col_dtyp => l_crt_rec.crit_col1_datatype,
267 p_value_set_id => l_crt_rec.col1_value_set_id,
268 p_range_flag => l_crt_rec.allow_range_validation_flag,
269 p_number_value1 => i.number_value1,
270 p_number_value2 => i.number_value2,
271 p_char_value1 => i.char_value1,
272 p_char_value2 => i.char_value2,
273 p_date_value1 => i.date_value1,
274 p_date_value2 => i.date_value2,
275 p_effective_date => p_effective_date);
276 if l_part2 = 'Y' then
277 l_node2_desc := '';
278
279 hr_utility.set_location('before node description 2',200);
280 hr_utility.set_location('before node 2 crit_col2_val_type_cd'||l_crt_rec.crit_col2_val_type_cd,200);
281 hr_utility.set_location('before node 2 l_crt_rec.col2_lookup_type'||l_crt_rec.col2_lookup_type,200);
282 hr_utility.set_location('before node 2 l_crt_rec.col2_value_set_id'||l_crt_rec.col2_value_set_id,200);
283 hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag2'||l_crt_rec.allow_range_validation_flag2,200);
284 hr_utility.set_location('before node 2 i.number_value3'||i.number_value3,200);
285 hr_utility.set_location('before node 2 i.char_value3'||i.char_value3,200);
286 hr_utility.set_location('before node 2 crit_col1_val_type_cd'||l_crt_rec.crit_col1_val_type_cd,200);
287 hr_utility.set_location('before node 2 l_crt_rec.col1_lookup_type'||l_crt_rec.col1_lookup_type,200);
288 hr_utility.set_location('before node 2 l_crt_rec.col1_value_set_id'||l_crt_rec.col1_value_set_id,200);
289 hr_utility.set_location('before node 2 l_crt_rec.allow_range_validation_flag'||l_crt_rec.allow_range_validation_flag,200);
290 hr_utility.set_location('before node 2 i.number_value1'||i.number_value1,200);
291 hr_utility.set_location('before node 2 i.char_value1'||i.char_value1,200);
292 if (l_crt_rec.crit_col2_val_type_cd = 'VAL_SET' and l_crt_rec.crit_col1_val_type_cd = 'VAL_SET') then
293 pqh_utility.get_valueset_sql(p_value_set_id => l_crt_rec.col2_value_set_id,
294 p_validation_type => l_valuset_val_type,
295 p_sql_stmt => l_valuset_sql_stmt,
296 p_error_status => l_valuset_err_st);
297 hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,1,100), 200);
298 hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,101,100), 200);
299 hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,201,100), 200);
300 hr_utility.set_location('value set query '||substr(l_valuset_sql_stmt,301,100), 200);
301 Select upper(flex_value_set_name)
302 into l_val_set_name
303 from fnd_flex_value_sets
304 where flex_value_set_id = l_crt_rec.col1_value_set_id;
305
306 hr_utility.set_location('parent value set name '||l_val_set_name, 30);
307
308 if instr(upper(l_valuset_sql_stmt),':$FLEX$.'||l_val_set_name ) > 0 then
309 if l_crt_rec.crit_col1_datatype = 'C' then
310 l_prnt_value1 := i.char_value1;
311 l_prnt_value2 := i.char_value2;
312 elsif l_crt_rec.crit_col1_datatype = 'N' then
313 l_prnt_value1 := to_number(i.number_value1);
314 l_prnt_value2 := to_number(i.number_value2);
315 end if;
316
317 if l_crt_rec.crit_col2_datatype = 'C' then
318 l_value3 := i.char_value3;
319 l_value4 := i.char_value4;
320 elsif l_crt_rec.crit_col2_datatype = 'N' then
321 l_value3 := to_number(i.number_value3);
322 l_value4 := to_number(i.number_value4);
323 end if;
324
325 l_node2_desc := pqh_utility.get_display_value(p_value => l_value3,
326 p_value_set_id => l_crt_rec.col2_value_set_id,
327 p_prnt_valset_nm => l_val_set_name,
328 p_prnt_value => l_prnt_value1);
329 if l_crt_rec.allow_range_validation_flag2 = 'Y' then
330 l_node2_desc := l_node2_desc||' - '||pqh_utility.get_display_value(p_value => l_value4,
331 p_value_set_id => l_crt_rec.col2_value_set_id,
332 p_prnt_valset_nm => l_val_set_name,
333 p_prnt_value => l_prnt_value1);
334 end if;
335 hr_utility.set_location('SJ node2 desc '||l_node2_desc, 30);
336 else
337 l_dep_val_set := 'N';
338 end if;
339 else
340 l_dep_val_set := 'N';
341 end if;
342 hr_utility.set_location('l_dep_val_set '||l_dep_val_set, 30);
343 if l_dep_val_set = 'N' then
344 l_node2_desc := node_value(p_val_type_cd => l_crt_rec.crit_col2_val_type_cd,
345 p_lookup_type => l_crt_rec.col2_lookup_type,
346 p_crit_col_dtyp => l_crt_rec.crit_col2_datatype,
347 p_value_set_id => l_crt_rec.col2_value_set_id,
348 p_range_flag => l_crt_rec.allow_range_validation_flag2,
349 p_number_value1 => i.number_value3,
350 p_number_value2 => i.number_value4,
351 p_char_value1 => i.char_value3,
352 p_char_value2 => i.char_value4,
353 p_date_value1 => i.date_value3,
354 p_date_value2 => i.date_value4,
355 p_effective_date => p_effective_date);
356 end if;
357
358 l_node1_desc := l_node1_desc||'/ '||l_node2_desc;
359 end if;
360 hr_utility.set_location('node desc is'||l_node1_desc,25);
361 if l_node_sum_desc is null then
362 l_node_sum_desc := l_node1_desc ;
363 else
364 l_node_sum_desc := l_node_sum_desc ||'; '||l_node1_desc ;
365 end if;
366 end loop;
367 hr_utility.set_location('node desc is'||l_node_sum_desc,25);
368 l_node_name := l_crt_rec.name ||' : '||l_node_sum_desc;
369 else
370 hr_utility.set_location('criteria shd be there',40);
371 end if;
372 hr_utility.set_location('node name is'||l_node_name,45);
373 return l_node_name;
374 end build_rate_node_name;
375 procedure create_matrix_txn(p_mode in varchar2,
376 p_business_group_id in number,
377 p_effective_date in date,
378 p_copy_entity_txn_id out nocopy number) is
379 l_rbc_txn_cat number;
380 l_ovn number;
381 begin
382 hr_utility.set_location('going for creating cet row',100);
383 begin
384 select transaction_category_id
385 into l_rbc_txn_cat
386 from pqh_transaction_categories
387 where short_name ='RBC_MATRIX'
388 and business_group_id is null;
389 hr_utility.set_location('txn_cat is'||l_rbc_txn_cat,100);
390 exception
391 when others then
392 hr_utility.set_location('txn_cat doesnot exist',100);
393 raise;
394 end;
395 if l_rbc_txn_cat is not null then
396 pqh_copy_entity_txns_api.create_COPY_ENTITY_TXN
397 (p_copy_entity_txn_id => p_copy_entity_txn_id
398 ,p_transaction_category_id => l_rbc_txn_cat
399 ,p_context_business_group_id => p_business_group_id
400 ,p_context => 'RBC_MATRIX'
401 ,p_action_date => p_effective_date
402 ,p_number_of_copies => 1
403 ,p_display_name => p_mode||' - RBC_MATRIX - '||to_char(sysdate,'ddmmyyyyhhmiss')
404 ,p_replacement_type_cd => 'NONE'
405 ,p_start_with => 'Rate Matrix'
406 ,p_status => p_mode
407 ,p_object_version_number => l_ovn
408 ,p_effective_date => p_effective_date
409 ) ;
410 end if;
411 exception
412 when others then
413 hr_utility.set_location('issues in creating CET row',100);
414 raise;
415 end create_matrix_txn;
416 procedure delete_matrix(p_copy_entity_txn_id in number) is
417 begin
418 delete from ben_copy_entity_results
419 where copy_entity_txn_id = p_copy_entity_txn_id ;
420 exception
421 when others then
422 hr_utility.set_location('issues in deleting cer rows ',10);
423 raise;
424 end delete_matrix;
425 function get_comp_flag(p_rate_comp_cd in varchar2) return varchar2 is
426 l_comp_flag varchar2(30);
427 begin
428 hr_utility.set_location('rate comp cd is'||p_rate_comp_cd,10);
429 if p_rate_comp_cd in ('AMOUNT','RULE') then
430 l_comp_flag := 'N';
431 elsif p_rate_comp_cd in ('PERCENT','ADD_TO') then
432 l_comp_flag := 'Y';
433 else
434 l_comp_flag := 'A';
435 end if;
436 hr_utility.set_location('comp flag is'||l_comp_flag,10);
437 return l_comp_flag;
438 end get_comp_flag;
439 procedure matx_rates(p_rate_matrix_id in number,
440 p_cet_id in number,
441 p_effective_date in date,
442 p_business_group_id in number,
443 p_rcr_tr_name in varchar2,
444 p_rcr_tr_id in number,
445 p_count out nocopy number) is
446 l_rcr_cer_id number;
447 l_rcr_cer_ovn number;
448 l_count number := 0;
449 cursor c_matx_rates is select distinct rate.criteria_rate_defn_id
450 from pqh_rate_matrix_rates_f rate, pqh_rate_matrix_nodes node
451 where node.pl_id = p_rate_matrix_id
452 and node.rate_matrix_node_id = rate.rate_matrix_node_id
453 and p_effective_date between effective_start_date and effective_end_date;
454 l_min varchar2(150);
455 l_mid varchar2(150);
456 l_max varchar2(150);
457 l_rate varchar2(150);
458 l_calc_mthd varchar2(150);
459 l_comp_flag varchar2(150);
460 l_crit_name varchar2(250);
461 l_currency_code pqh_criteria_rate_defn.currency_code%type;
462 l_uom pqh_criteria_rate_defn.uom%type;
463 begin
464 hr_utility.set_location('inside copying matrix_rates ',10);
465 for matx_rate in c_matx_rates loop
466 hr_utility.set_location('copying matx_rate to staging'||l_count,20);
467 begin
468 select define_min_rate_flag,define_mid_rate_flag,define_max_rate_flag,define_std_rate_flag,rate_calc_cd, name,currency_code,uom
469 into l_min,l_mid,l_max,l_rate,l_calc_mthd,l_crit_name,l_currency_code,l_uom
470 from pqh_criteria_rate_defn_vl
471 where criteria_rate_defn_id = matx_rate.criteria_rate_defn_id;
472 exception
473 when others then
474 hr_utility.set_location('issue in getting rate defn det',30);
475 raise;
476 end;
477 l_comp_flag := get_comp_flag(l_calc_mthd);
478 ben_copy_entity_results_api.create_copy_entity_results(
479 p_effective_date => p_effective_date
480 ,p_copy_entity_txn_id => p_cet_id
481 ,p_result_type_cd => 'DISPLAY'
482 ,p_table_name => p_rcr_tr_name
483 ,p_table_route_id => p_rcr_tr_id
484 ,p_table_alias => 'RCR'
485 ,p_dml_operation => 'COPIED'
486 ,p_information1 => matx_rate.criteria_rate_defn_id
487 ,p_information4 => p_business_group_id
488 ,p_information5 => l_crit_name
489 ,p_information49 => l_uom
490 ,p_information50 => l_currency_code
491 ,p_information160 => l_count
492 ,p_information111 => l_min
493 ,p_information112 => l_mid
494 ,p_information113 => l_max
495 ,p_information114 => l_rate
496 ,p_information115 => l_calc_mthd
497 ,p_information116 => l_comp_flag
498 ,p_copy_entity_result_id => l_rcr_cer_id
499 ,p_object_version_number => l_rcr_cer_ovn);
500 hr_utility.set_location('rcr cer row is '||l_rcr_cer_id,30);
501 l_count := l_count + 1;
502 end loop;
503 hr_utility.set_location('total matx rates'||l_count,50);
504 p_count := l_count;
505 exception
506 when others then
507 hr_utility.set_location('issues in pulling matx rates',10);
508 raise;
509 end matx_rates;
510 procedure get_rate_value(p_rmn_cer_id in number,
511 p_crit_rate_defn_id in number,
512 p_cet_id in number,
513 p_effective_date in date,
514 p_min out nocopy number,
515 p_mid out nocopy number,
516 p_max out nocopy number,
517 p_rate out nocopy number,
518 p_currency_cd out nocopy varchar2,
519 p_freq_cd out nocopy varchar2) is
520 l_rate_rec ben_copy_entity_results%rowtype;
521 begin
522 select *
523 into l_rate_rec
524 from ben_copy_entity_results
525 where copy_entity_txn_id = p_cet_id
526 and information162 = p_crit_rate_defn_id
527 and table_alias = 'RMR'
528 and parent_entity_result_id = p_rmn_cer_id
529 and dml_operation <> 'DELETE'
530 and p_effective_date between information2 and nvl(information3,to_date('31/12/4712','dd/mm/yyyy'));
531 p_min := l_rate_rec.information294;
532 p_max := l_rate_rec.information295;
533 p_mid := l_rate_rec.information296;
534 p_rate := l_rate_rec.information297;
535 begin
536 select currency_code,reference_period_cd
537 into p_currency_cd,p_freq_cd
538 from pqh_criteria_rate_defn
539 where criteria_rate_defn_id = p_crit_rate_defn_id;
540 exception
541 when no_data_found then
542 hr_utility.set_location('crit rate not exists ',15);
543 raise;
544 when others then
545 hr_utility.set_location('issues in pulling crit rate',20);
546 raise;
547 end ;
548 exception
549 when no_data_found then
550 hr_utility.set_location('no value for node and rate',10);
551 when others then
552 hr_utility.set_location('issues in pulling rate value',20);
553 raise;
554 end get_rate_value;
555 procedure get_rate_value(p_rate_matrix_node_id in number,
556 p_crit_rate_defn_id in number,
557 p_effective_date in date,
558 p_min out nocopy number,
559 p_mid out nocopy number,
560 p_max out nocopy number,
561 p_rate out nocopy number) is
562 l_rate_rec pqh_rate_matrix_rates_f%rowtype;
563 begin
564 select *
565 into l_rate_rec
566 from pqh_rate_matrix_rates_f
567 where rate_matrix_node_id = p_rate_matrix_node_id
568 and criteria_rate_defn_id = p_crit_rate_defn_id
569 and p_effective_date between effective_start_date and effective_end_date;
570 p_min := l_rate_rec.min_rate_value;
571 p_mid := l_rate_rec.mid_rate_value;
572 p_max := l_rate_rec.max_rate_value;
573 p_rate := l_rate_rec.rate_value;
574 exception
575 when no_data_found then
576 hr_utility.set_location('no value for node and rate',10);
577 when others then
578 hr_utility.set_location('issues in pulling rate value',20);
579 raise;
580 end get_rate_value;
581 function get_annual_factor(p_freq_cd in varchar2) return number is
582 begin
583 if p_freq_cd = 'BWK' then
584 return 27;
585 elsif p_freq_cd = 'MO' then
586 return 12;
587 elsif p_freq_cd = 'PQU' then
588 return 4;
589 elsif p_freq_cd = 'PWK' then
590 return 52;
591 elsif p_freq_cd = 'PYR' then
592 return 1;
593 elsif p_freq_cd = 'SAN' then
594 return 2;
595 elsif p_freq_cd = 'SMO' then
596 return 24;
597 else
598 hr_utility.set_location('invalid freq passed',10);
599 return -1;
600 end if;
601 end get_annual_factor;
602 procedure get_comp_value(p_rate_defn_id in number,
603 p_rounding_code in number,
604 p_rmn_cer_id in number,
605 p_effective_date in date,
606 p_cet_id in number,
607 p_min in number,
608 p_mid in number,
609 p_max in number,
610 p_rate in number,
611 p_min_c out nocopy varchar2,
612 p_mid_c out nocopy varchar2,
613 p_max_c out nocopy varchar2,
614 p_rate_c out nocopy varchar2) is
615 cursor crit_rate_factors (p_criteria_rate_defn_id number) is
616 select * from pqh_criteria_rate_factors
617 where criteria_rate_defn_id = p_criteria_rate_defn_id;
618 l_crit_rate_rec pqh_criteria_rate_defn%rowtype;
619 l_rate_min number;
620 l_rate_mid number;
621 l_rate_max number;
622 l_rate_value number;
623 l_rate_c number;
624 l_min_c number;
625 l_mid_c number;
626 l_max_c number;
627 l_freq_conv number;
628 l_curr_conv number;
629 l_rt_freq_ann number;
630 l_ref_freq_ann number;
631 l_curr_cd varchar2(30);
632 l_freq_cd varchar2(30);
633 begin
634 hr_utility.set_location('get computed value for RMN'||p_rmn_cer_id,10);
635 hr_utility.set_location(' CRT'||p_rate_defn_id,10);
636 begin
637 select * into l_crit_rate_rec
638 from pqh_criteria_rate_defn
639 where criteria_rate_defn_id = p_rate_defn_id;
640 exception
641 when no_data_found then
642 hr_utility.set_location('no rate exists'||p_rate_defn_id,20);
643 raise ;
644 when others then
645 hr_utility.set_location('rate fetch causing issues'||p_rate_defn_id,30);
646 raise ;
647 end;
648 if l_crit_rate_rec.rate_calc_cd in ('AMOUNT','RULE') then
649 hr_utility.set_location('rate of type amt ',30);
650 p_min_c := null;
651 p_mid_c := null;
652 p_max_c := null;
653 p_rate_c := null;
654 elsif l_crit_rate_rec.rate_calc_cd in ('ADD_TO','PERCENT','SUM') then
655 hr_utility.set_location('rate of type '||l_crit_rate_rec.rate_calc_cd,40);
656 for i in crit_rate_factors(p_rate_defn_id) loop
657 hr_utility.set_location('rate factors pulled ',45);
658 get_rate_value(p_rmn_cer_id => p_rmn_cer_id,
659 p_cet_id => p_cet_id,
660 p_crit_rate_defn_id => i.parent_criteria_rate_defn_id,
661 p_effective_date => p_effective_date,
662 p_min => l_rate_min,
663 p_mid => l_rate_mid,
664 p_max => l_rate_max,
665 p_rate => l_rate_value,
666 p_currency_cd => l_curr_cd,
667 p_freq_cd => l_freq_cd);
668 hr_utility.set_location('parent value of rate '||l_rate_value,45);
669 hr_utility.set_location('parent value of min '||l_rate_min,45);
670 hr_utility.set_location('parent value of mid '||l_rate_mid,45);
671 hr_utility.set_location('parent value of max '||l_rate_max,45);
672 if l_freq_cd <> l_crit_rate_rec.reference_period_cd then
673 -- get the conv factor between frequencies
674 l_rt_freq_ann := get_annual_factor(l_freq_cd);
675 l_ref_freq_ann := get_annual_factor(l_crit_rate_rec.reference_period_cd);
676 l_freq_conv := l_rt_freq_ann/l_ref_freq_ann;
677 else
678 l_freq_conv := 1;
679 end if;
680 hr_utility.set_location('freq conv fctr is '||l_freq_conv,46);
681 if l_curr_cd <> l_crit_rate_rec.currency_code then
682 hr_utility.set_location('Conv from '||l_curr_cd||' To '||l_crit_rate_rec.currency_code,46);
683 -- get the conv factor between currencies from gl_daily_rates
684 begin
685 select conversion_rate
686 into l_curr_conv
687 from gl_daily_rates
688 where from_currency = l_curr_cd
689 and to_currency = l_crit_rate_rec.currency_code
690 and conversion_date = (select max(conversion_date)
691 from gl_daily_rates
692 where from_currency = l_curr_cd
693 and to_currency = l_crit_rate_rec.currency_code
694 and conversion_date <=p_effective_date);
695 exception
696 when no_data_found then
697 hr_utility.set_location('rates not exist',25);
698 l_curr_conv := 1;
699 when others then
700 hr_utility.set_location('daily rates pull error',25);
701 raise;
702 end;
703 else
704 l_curr_conv := 1;
705 end if;
706 hr_utility.set_location('curr conv factr is'||l_curr_conv,28);
707 l_rate_min := (l_freq_conv * nvl(l_rate_min,0)*l_curr_conv);
708 l_rate_mid := (l_freq_conv * nvl(l_rate_mid,0)*l_curr_conv);
709 l_rate_max := (l_freq_conv * nvl(l_rate_max,0)*l_curr_conv);
710 l_rate_value := (l_freq_conv * nvl(l_rate_value,0)*l_curr_conv);
711 hr_utility.set_location('conv. parent value of rate '||l_rate_value,45);
712 hr_utility.set_location('conv. parent value of min '||l_rate_min,45);
713 hr_utility.set_location('conv. parent value of mid '||l_rate_mid,45);
714 hr_utility.set_location('conv. parent value of max '||l_rate_max,45);
715 if l_crit_rate_rec.rate_calc_cd = 'ADD_TO' then
716 if l_rate_min is not null then
717 l_min_c := l_rate_min + nvl(p_min,0);
718 end if;
719 if l_rate_mid is not null then
720 l_mid_c := l_rate_mid + nvl(p_mid,0);
721 end if;
722 if l_rate_max is not null then
723 l_max_c := l_rate_max + nvl(p_max,0);
724 end if;
725 if l_rate_value is not null then
726 l_rate_c := l_rate_value + nvl(p_rate,0);
727 end if;
728 end if;
729 if l_crit_rate_rec.rate_calc_cd = 'SUM' then
730 if l_rate_min is not null then
731 l_min_c := l_rate_min + nvl(l_min_c,0);
732 end if;
733 if l_rate_mid is not null then
734 l_mid_c := l_rate_mid + nvl(l_mid_c,0);
735 end if;
736 if l_rate_max is not null then
737 l_max_c := l_rate_max + nvl(l_max_c,0);
738 end if;
739 if l_rate_value is not null then
740 l_rate_c := l_rate_value + nvl(l_rate_c,0);
741 end if;
742 end if;
743 if l_crit_rate_rec.rate_calc_cd = 'PERCENT' then
744 if l_rate_min is not null then
745 l_min_c := l_rate_min * nvl(p_min,0) / 100 ;
746 end if;
747 if l_rate_mid is not null then
748 l_mid_c := l_rate_mid * nvl(p_mid,0) / 100 ;
749 end if;
750 if l_rate_max is not null then
751 l_max_c := l_rate_max * nvl(p_max,0) / 100 ;
752 end if;
753 if l_rate_value is not null then
754 l_rate_c := l_rate_value * nvl(p_rate,0) / 100 ;
755 end if;
756 end if;
757 end loop;
758 p_min_c := to_char(round(l_min_c,p_rounding_code));
759 p_mid_c := to_char(round(l_mid_c,p_rounding_code));
760 p_max_c := to_char(round(l_max_c,p_rounding_code));
761 p_rate_c := to_char(round(l_rate_c,p_rounding_code));
762 end if;
763 end get_comp_value;
764 procedure build_rbr_for_rmn(p_rmn_cer_id in number,
765 p_rbr_cer_id in number,
766 p_cet_id in number,
767 p_effective_date in date,
768 p_rbr_tr_id in number,
769 p_rbr_tr_name in varchar2) is
770 cursor csr_rate_types is select *
771 from ben_copy_entity_results
772 where copy_entity_txn_id = p_cet_id
773 and table_alias = 'RCR'
774 order by information160; -- in rate order
775 cursor csr_rates (p_rate_defn_id in number, p_node_cer_id in number) is
776 select copy_entity_result_id,information294,information295,information296,information297
777 from ben_copy_entity_results
778 where copy_entity_txn_id = p_cet_id
779 and table_alias = 'RMR'
780 and information162 = p_rate_defn_id
781 and parent_entity_result_id = p_node_cer_id and dml_operation <> 'DELETE';
782 l_rbr_cer_id number;
783 l_rbr_cer_ovn number;
784 l_min number;
785 l_mid number;
786 l_max number;
787 l_rate number;
788 l_rmr_cer_id number;
789 l_min_c varchar2(30);
790 l_mid_c varchar2(30);
791 l_max_c varchar2(30);
792 l_rate_c varchar2(30);
793
794 min1 number;
795 min1_c varchar2(30);
796 min1_flag varchar2(30);
797 mid1 number;
798 mid1_c varchar2(30);
799 mid1_flag varchar2(30);
800 max1 number;
801 max1_c varchar2(30);
802 max1_flag varchar2(30);
803 rate1 number;
804 rate1_c varchar2(30);
805 rate1_flag varchar2(30);
806 rmr1_cer_id number;
807 comp1_flag varchar2(30);
808
809 min2 number;
810 min2_c varchar2(30);
811 min2_flag varchar2(30);
812 mid2 number;
813 mid2_c varchar2(30);
814 mid2_flag varchar2(30);
815 max2 number;
816 max2_c varchar2(30);
817 max2_flag varchar2(30);
818 rate2 number;
819 rate2_c varchar2(30);
820 rate2_flag varchar2(30);
821 rmr2_cer_id number;
822 comp2_flag varchar2(30);
823
824 min3 number;
825 min3_c varchar2(30);
826 min3_flag varchar2(30);
827 mid3 number;
828 mid3_c varchar2(30);
829 mid3_flag varchar2(30);
830 max3 number;
831 max3_c varchar2(30);
832 max3_flag varchar2(30);
833 rate3 number;
834 rate3_c varchar2(30);
835 rate3_flag varchar2(30);
836 rmr3_cer_id number;
837 comp3_flag varchar2(30);
838
839 min4 number;
840 min4_c varchar2(30);
841 min4_flag varchar2(30);
842 mid4 number;
843 mid4_c varchar2(30);
844 mid4_flag varchar2(30);
845 max4 number;
846 max4_c varchar2(30);
847 max4_flag varchar2(30);
848 rate4 number;
849 rate4_c varchar2(30);
850 rate4_flag varchar2(30);
851 rmr4_cer_id number;
852 comp4_flag varchar2(30);
853
854 begin
855 hr_utility.set_location('inside build_matx',10);
856 for rate_types in csr_rate_types loop
857 open csr_rates(rate_types.information1,p_rmn_cer_id);
858 fetch csr_rates into l_rmr_cer_id,l_min,l_max,l_mid,l_rate;
859 if csr_rates%notfound then
860 hr_utility.set_location('rate not found, nulling ',10);
861 hr_utility.set_location('rate type is '||rate_types.information1,10);
862 hr_utility.set_location('rmn_cer is '||p_rmn_cer_id,10);
863 l_min := '';
864 l_mid := '';
865 l_max := '';
866 l_rate := '';
867 l_min_c := '';
868 l_mid_c := '';
869 l_max_c := '';
870 l_rate_c := '';
871 l_rmr_cer_id := '';
872 else
873 hr_utility.set_location('rate value '||l_rate,20);
874 get_comp_value(p_rate_defn_id => rate_types.information1,
875 p_rounding_code => 3,
876 p_cet_id => p_cet_id,
877 p_rmn_cer_id => p_rmn_cer_id,
878 p_effective_date => p_effective_date,
879 p_min => l_min,
880 p_mid => l_mid,
881 p_max => l_max,
882 p_rate => l_rate,
883 p_min_c => l_min_c,
884 p_mid_c => l_mid_c,
885 p_max_c => l_max_c,
886 p_rate_c => l_rate_c);
887 hr_utility.set_location('comp rate '||l_rate_c,20);
888 hr_utility.set_location('comp min '||l_min_c,20);
889 hr_utility.set_location('comp mid '||l_mid_c,20);
890 hr_utility.set_location('comp max '||l_max_c,20);
891 end if;
892 close csr_rates;
893 if rate_types.information160 = 0 then
894 hr_utility.set_location('1st rate values set ',20);
895 min1 := l_min;
896 mid1 := l_mid;
897 max1 := l_max;
898 rate1 := l_rate;
899 rmr1_cer_id := l_rmr_cer_id;
900 min1_flag := rate_types.information111;
901 mid1_flag := rate_types.information112;
902 max1_flag := rate_types.information113;
903 rate1_flag := rate_types.information114;
904 comp1_flag := rate_types.information116;
905 min1_c := l_min_c;
906 mid1_c := l_mid_c;
907 max1_c := l_max_c;
908 rate1_c := l_rate_c;
909 end if;
910 if rate_types.information160 = 1 then
911 hr_utility.set_location('2nd rate values set ',20);
912 min2 := l_min;
913 mid2 := l_mid;
914 max2 := l_max;
915 rate2 := l_rate;
916 rmr2_cer_id := l_rmr_cer_id;
917 min2_flag := rate_types.information111;
918 mid2_flag := rate_types.information112;
919 max2_flag := rate_types.information113;
920 rate2_flag := rate_types.information114;
921 comp2_flag := rate_types.information116;
922 min2_c := l_min_c;
923 mid2_c := l_mid_c;
924 max2_c := l_max_c;
925 rate2_c := l_rate_c;
926 end if;
927 if rate_types.information160 = 2 then
928 min3 := l_min;
929 mid3 := l_mid;
930 max3 := l_max;
931 rate3 := l_rate;
932 rmr3_cer_id := l_rmr_cer_id;
933 min3_flag := rate_types.information111;
934 mid3_flag := rate_types.information112;
935 max3_flag := rate_types.information113;
936 rate3_flag := rate_types.information114;
937 comp3_flag := rate_types.information116;
938 min3_c := l_min_c;
939 mid3_c := l_mid_c;
940 max3_c := l_max_c;
941 rate3_c := l_rate_c;
942 end if;
943 if rate_types.information160 = 3 then
944 min4 := l_min;
945 mid4 := l_mid;
946 max4 := l_max;
947 rate4 := l_rate;
948 rmr4_cer_id := l_rmr_cer_id;
949 min4_flag := rate_types.information111;
950 mid4_flag := rate_types.information112;
951 max4_flag := rate_types.information113;
952 rate4_flag := rate_types.information114;
953 comp4_flag := rate_types.information116;
954 min4_c := l_min_c;
955 mid4_c := l_mid_c;
956 max4_c := l_max_c;
957 rate4_c := l_rate_c;
958 end if;
959 end loop;
960 if p_rbr_cer_id is null then
961 ben_copy_entity_results_api.create_copy_entity_results(
962 p_effective_date => p_effective_date
963 ,p_copy_entity_txn_id => p_cet_id
964 ,p_result_type_cd => 'DISPLAY'
965 ,p_table_name => p_rbr_tr_name
966 ,p_table_route_id => p_rbr_tr_id
967 ,p_table_alias => 'RBR'
968 ,p_dml_operation => 'COPIED'
969 ,p_Information287 => min1
970 ,p_Information288 => mid1
971 ,p_Information289 => max1
972 ,p_Information290 => rate1
973 ,p_Information160 => rmr1_cer_id
974 ,p_Information36 => min1_c
975 ,p_Information37 => mid1_c
976 ,p_Information38 => max1_c
977 ,p_Information39 => rate1_c
978 ,p_Information11 => min1_flag
979 ,p_Information12 => mid1_flag
980 ,p_Information13 => max1_flag
981 ,p_Information14 => rate1_flag
982 ,p_Information15 => comp1_flag
983 ,p_Information293 => min2
984 ,p_Information294 => mid2
985 ,p_Information295 => max2
986 ,p_Information296 => rate2
987 ,p_Information161 => rmr2_cer_id
988 ,p_Information40 => min2_c
989 ,p_Information41 => mid2_c
990 ,p_Information42 => max2_c
991 ,p_Information43 => rate2_c
992 ,p_Information16 => min2_flag
993 ,p_Information17 => mid2_flag
994 ,p_Information18 => max2_flag
995 ,p_Information19 => rate2_flag
996 ,p_Information20 => comp2_flag
997 ,p_Information297 => min3
998 ,p_Information298 => mid3
999 ,p_Information299 => max3
1000 ,p_Information300 => rate3
1001 ,p_Information162 => rmr3_cer_id
1002 ,p_Information44 => min3_c
1003 ,p_Information45 => mid3_c
1004 ,p_Information46 => max3_c
1005 ,p_Information47 => rate3_c
1006 ,p_Information21 => min3_flag
1007 ,p_Information22 => mid3_flag
1008 ,p_Information23 => max3_flag
1009 ,p_Information24 => rate3_flag
1010 ,p_Information25 => comp3_flag
1011 ,p_Information301 => min4
1012 ,p_Information302 => mid4
1013 ,p_Information303 => max4
1014 ,p_Information304 => rate4
1015 ,p_Information169 => rmr4_cer_id
1016 ,p_Information48 => min4_c
1017 ,p_Information49 => mid4_c
1018 ,p_Information50 => max4_c
1019 ,p_Information51 => rate4_c
1020 ,p_Information26 => min4_flag
1021 ,p_Information27 => mid4_flag
1022 ,p_Information28 => max4_flag
1023 ,p_Information29 => rate4_flag
1024 ,p_Information30 => comp4_flag
1025 ,p_information1 => p_rmn_cer_id
1026 ,p_copy_entity_result_id => l_rbr_cer_id
1027 ,p_object_version_number => l_rbr_cer_ovn);
1028 hr_utility.set_location('rbr row created',20);
1029 else
1030 l_rbr_cer_ovn := pqh_gsp_stage_to_ben.get_ovn(
1031 p_table_name => 'BEN_COPY_ENTITY_RESULTS',
1032 p_key_column_name => 'COPY_ENTITY_RESULT_ID',
1033 p_key_column_value => p_rbr_cer_id,
1034 p_effective_date => '');
1035 hr_utility.set_location('rbr ovn is'||l_rbr_cer_ovn,10);
1036 ben_copy_entity_results_api.update_copy_entity_results(
1037 p_effective_date => p_effective_date
1038 ,p_copy_entity_txn_id => p_cet_id
1039 ,p_result_type_cd => 'DISPLAY'
1040 ,p_table_name => p_rbr_tr_name
1041 ,p_table_route_id => p_rbr_tr_id
1042 ,p_table_alias => 'RBR'
1043 ,p_Information287 => min1
1044 ,p_Information288 => mid1
1045 ,p_Information289 => max1
1046 ,p_Information290 => rate1
1047 ,p_Information160 => rmr1_cer_id
1048 ,p_Information36 => min1_c
1049 ,p_Information37 => mid1_c
1050 ,p_Information38 => max1_c
1051 ,p_Information39 => rate1_c
1052 ,p_Information11 => min1_flag
1053 ,p_Information12 => mid1_flag
1054 ,p_Information13 => max1_flag
1055 ,p_Information14 => rate1_flag
1056 ,p_Information15 => comp1_flag
1057 ,p_Information293 => min2
1058 ,p_Information294 => mid2
1059 ,p_Information295 => max2
1060 ,p_Information296 => rate2
1061 ,p_Information161 => rmr2_cer_id
1062 ,p_Information40 => min2_c
1063 ,p_Information41 => mid2_c
1064 ,p_Information42 => max2_c
1065 ,p_Information43 => rate2_c
1066 ,p_Information16 => min2_flag
1067 ,p_Information17 => mid2_flag
1068 ,p_Information18 => max2_flag
1069 ,p_Information19 => rate2_flag
1070 ,p_Information20 => comp2_flag
1071 ,p_Information297 => min3
1072 ,p_Information298 => mid3
1073 ,p_Information299 => max3
1074 ,p_Information300 => rate3
1075 ,p_Information162 => rmr3_cer_id
1076 ,p_Information44 => min3_c
1077 ,p_Information45 => mid3_c
1078 ,p_Information46 => max3_c
1079 ,p_Information47 => rate3_c
1080 ,p_Information21 => min3_flag
1081 ,p_Information22 => mid3_flag
1082 ,p_Information23 => max3_flag
1083 ,p_Information24 => rate3_flag
1084 ,p_Information25 => comp3_flag
1085 ,p_Information301 => min4
1086 ,p_Information302 => mid4
1087 ,p_Information303 => max4
1088 ,p_Information304 => rate4
1089 ,p_Information169 => rmr4_cer_id
1090 ,p_Information48 => min4_c
1091 ,p_Information49 => mid4_c
1092 ,p_Information50 => max4_c
1093 ,p_Information51 => rate4_c
1094 ,p_Information26 => min4_flag
1095 ,p_Information27 => mid4_flag
1096 ,p_Information28 => max4_flag
1097 ,p_Information29 => rate4_flag
1098 ,p_Information30 => comp4_flag
1099 ,p_information1 => p_rmn_cer_id
1100 ,p_copy_entity_result_id => p_rbr_cer_id
1101 ,p_object_version_number => l_rbr_cer_ovn
1102 ,p_information323 => '');
1103 hr_utility.set_location('rbr row updated',20);
1104 end if;
1105 end build_rbr_for_rmn;
1106 procedure recalc_rate_matx(p_cet_id in number,
1107 p_effective_date in date) is
1108 cursor c1 is select *
1109 from ben_copy_entity_results
1110 where copy_entity_txn_id = p_cet_id
1111 and table_alias = 'RBR'
1112 and nvl(dml_operation,'UPDATE') = 'UPDATE';
1113 l_rbr_tr_id number;
1114 l_rbr_tr_name varchar2(150);
1115
1116 begin
1117 hr_utility.set_location('inside recalc',10);
1118 pqh_gsp_hr_to_stage.get_table_route_details
1119 (p_table_alias => 'RBR',
1120 p_table_route_id => l_rbr_tr_id,
1121 p_table_name => l_rbr_tr_name);
1122 hr_utility.set_location('tr name is '||l_rbr_tr_name,10);
1123 for i in c1 loop
1124 rbr_writeback(p_rbr_cer_id => i.copy_entity_result_id,
1125 p_effective_date => p_effective_date);
1126 hr_utility.set_location('rbr wrtbk comple for rmn_cer'||i.information1,22);
1127 build_rbr_for_rmn(p_rmn_cer_id => i.information1,
1128 p_rbr_cer_id => i.copy_entity_result_id,
1129 p_cet_id => p_cet_id,
1130 p_effective_date => p_effective_date,
1131 p_rbr_tr_id => l_rbr_tr_id,
1132 p_rbr_tr_name => l_rbr_tr_name);
1133 hr_utility.set_location('rbr row built ',22);
1134 end loop;
1135 end recalc_rate_matx;
1136 procedure build_rate_matx(p_cet_id in number,
1137 p_effective_date in date) is
1138 cursor csr_nodes is select copy_entity_result_id
1139 from ben_copy_entity_results
1140 where copy_entity_txn_id = p_cet_id
1141 and table_alias = 'RMN'
1142 and dml_operation <> 'DELETE';
1143 cursor csr_rbr(p_rmn_cer_id number) is
1144 select copy_entity_result_id
1145 from ben_copy_entity_results
1146 where copy_entity_txn_id = p_cet_id
1147 and table_alias = 'RBR'
1148 and information1 = p_rmn_cer_id;
1149 l_rbr_tr_id number;
1150 l_rbr_tr_name varchar2(150);
1151 l_rbr_cer_id number;
1152 begin
1153 hr_utility.set_location('inside build_matx',10);
1154 pqh_gsp_hr_to_stage.get_table_route_details
1155 (p_table_alias => 'RBR',
1156 p_table_route_id => l_rbr_tr_id,
1157 p_table_name => l_rbr_tr_name);
1158 hr_utility.set_location('tr name is '||l_rbr_tr_name,10);
1159 for node in csr_nodes loop
1160 hr_utility.set_location('rmn cer is '||node.copy_entity_result_id,20);
1161 open csr_rbr(node.copy_entity_result_id);
1162 fetch csr_rbr into l_rbr_cer_id;
1163 if csr_rbr%notfound then
1164 hr_utility.set_location('rbr cer is '||l_rbr_cer_id,10);
1165 l_rbr_cer_id := '';
1166 end if;
1167 close csr_rbr;
1168 build_rbr_for_rmn(p_rmn_cer_id => node.copy_entity_result_id,
1169 p_rbr_cer_id => l_rbr_cer_id,
1170 p_cet_id => p_cet_id,
1171 p_effective_date => p_effective_date,
1172 p_rbr_tr_id => l_rbr_tr_id,
1173 p_rbr_tr_name => l_rbr_tr_name);
1174 end loop;
1175 end build_rate_matx;
1176 procedure write_rmr_row(p_rmr_cer_id in number,
1177 p_rmn_cer_id in number,
1178 p_cet_id in number,
1179 p_effective_date in date,
1180 p_rate_level in number,
1181 p_min in number,
1182 p_mid in number,
1183 p_max in number,
1184 p_value in number) is
1185 l_rcr_rec ben_copy_entity_results%rowtype;
1186 l_rmr_cer_id number;
1187 l_rmr_cer_ovn number;
1188 l_rmr_tr_id number;
1189 l_rmr_tr_name varchar2(80);
1190 l_rmr_rec ben_copy_entity_results%rowtype;
1191 l_dml_operation varchar2(30);
1192 cursor c1 is select * from ben_copy_entity_results
1193 where copy_entity_result_id = p_rmr_cer_id
1194 for update of dml_operation;
1195 begin
1196 if p_rmr_cer_id is null then
1197 begin
1198 select * into l_rcr_rec
1199 from ben_copy_entity_results
1200 where copy_entity_txn_id = p_cet_id
1201 and table_alias ='RCR'
1202 and information160 = p_rate_level;
1203 exception
1204 when no_data_found then
1205 hr_utility.set_location('Rate does not exist',10);
1206 raise;
1207 when others then
1208 hr_utility.set_location('Rate retrival issue ',20);
1209 raise;
1210 end;
1211 pqh_gsp_hr_to_stage.get_table_route_details
1212 (p_table_alias => 'RMR',
1213 p_table_route_id => l_rmr_tr_id,
1214 p_table_name => l_rmr_tr_name);
1215 hr_utility.set_location('rmr tr name is '||l_rmr_tr_name,10);
1216
1217 hr_utility.set_location('new rmr row to be cred, rate type'||p_rate_level,10);
1218 ben_copy_entity_results_api.create_copy_entity_results(
1219 p_effective_date => p_effective_date
1220 ,p_copy_entity_txn_id => p_cet_id
1221 ,p_result_type_cd => 'DISPLAY'
1222 ,p_table_name => l_rmr_tr_name
1223 ,p_table_route_id => l_rmr_tr_id
1224 ,p_table_alias => 'RMR'
1225 ,p_dml_operation => 'CREATE'
1226 ,p_information2 => p_effective_date
1227 ,p_information4 => l_rcr_rec.information4
1228 ,p_information294 => p_min
1229 ,p_information295 => p_max
1230 ,p_information296 => p_mid
1231 ,p_information297 => p_value
1232 ,p_information162 => l_rcr_rec.information1
1233 ,p_parent_entity_result_id => p_rmn_cer_id
1234 ,p_copy_entity_result_id => l_rmr_cer_id
1235 ,p_object_version_number => l_rmr_cer_ovn);
1236 hr_utility.set_location('rmr cer row is '||l_rmr_cer_id,30);
1237 else
1238 hr_utility.set_location('existing rmr row to be upd',30);
1239 open c1;
1240 fetch c1 into l_rmr_rec;
1241 if c1%notfound then
1242 hr_utility.set_location('rmr row doesnot exist',30);
1243 else
1244 if nvl(l_rmr_rec.dml_operation,'COPIED') = 'COPIED' then
1245 l_dml_operation := 'UPDATE';
1246 else
1247 l_dml_operation := l_rmr_rec.dml_operation;
1248 end if;
1249 update ben_copy_entity_results
1250 set information294 = p_min,
1251 information295 = p_max,
1252 information296 = p_mid,
1253 information297 = p_value,
1254 dml_operation = l_dml_operation
1255 where current of c1;
1256 hr_utility.set_location('rmr row upd',30);
1257 end if;
1258 close c1;
1259 end if;
1260 end write_rmr_row;
1261 procedure set_rmn_stat(p_rmn_cer_id in number) is
1262 cursor c1 is select dml_operation
1263 from ben_copy_entity_results
1264 where copy_entity_result_id = p_rmn_cer_id
1265 for update of dml_operation;
1266 l_stat varchar2(80);
1267 begin
1268 open c1;
1269 fetch c1 into l_stat;
1270 if c1%found then
1271 if l_stat not in ('CREATE','UPDATE') then
1272 update ben_copy_entity_results
1273 set dml_operation ='UPDATE'
1274 where current of c1;
1275 else
1276 hr_utility.set_location('rmn stat'||l_stat,20);
1277 end if;
1278 else
1279 hr_utility.set_location('no rmn'||p_rmn_cer_id,10);
1280 end if;
1281 close c1;
1282 exception
1283 when no_data_found then
1284 hr_utility.set_location('no rmn'||p_rmn_cer_id,10);
1285 raise;
1286 when others then
1287 hr_utility.set_location('issues in getting rmn'||p_rmn_cer_id,10);
1288 raise;
1289 end set_rmn_stat;
1290 procedure rbr_writeback(p_cet_id in number,
1291 p_effective_date in date) is
1292 cursor c1 is select * from ben_copy_entity_results
1293 where copy_entity_txn_id = p_cet_id
1294 and table_alias ='RBR'
1295 and nvl(dml_operation,'UPDATE') = 'UPDATE'
1296 for update of dml_operation;
1297 begin
1298 for i in c1 loop
1299 rbr_writeback(p_rbr_cer_id => i.copy_entity_result_id,
1300 p_effective_date => p_effective_date);
1301 update ben_copy_entity_results
1302 set dml_operation = 'COPIED'
1303 where current of c1;
1304 end loop;
1305 end rbr_writeback;
1306 procedure rbr_writeback(p_rbr_cer_id in number,
1307 p_effective_date in date) is
1308 l_rbr_rec ben_copy_entity_results%rowtype;
1309 l_rate1_exists varchar2(30);
1310 l_rate2_exists varchar2(30);
1311 l_rate3_exists varchar2(30);
1312 l_rate4_exists varchar2(30);
1313 begin
1314 begin
1315 select * into l_rbr_rec
1316 from ben_copy_entity_results
1317 where copy_entity_result_id = p_rbr_cer_id;
1318 exception
1319 when no_data_found then
1320 hr_utility.set_location('invalid rbr cer passed'||p_rbr_cer_id,10);
1321 raise;
1322 when others then
1323 hr_utility.set_location('issue in rbr pull'||p_rbr_cer_id,10);
1324 raise;
1325 end;
1326 if l_rbr_rec.information160 is not null or
1327 l_rbr_rec.information15 is not null or
1328 l_rbr_rec.information287 is not null or
1329 l_rbr_rec.information288 is not null or
1330 l_rbr_rec.information289 is not null or
1331 l_rbr_rec.information290 is not null then
1332 hr_utility.set_location('1st rmr exists with val '||l_rbr_rec.information290,10);
1333 l_rate1_exists := 'Y';
1334 else
1335 l_rate1_exists := 'N';
1336 end if;
1337 if l_rbr_rec.information161 is not null or
1338 l_rbr_rec.information20 is not null or
1339 l_rbr_rec.information293 is not null or
1340 l_rbr_rec.information294 is not null or
1341 l_rbr_rec.information295 is not null or
1342 l_rbr_rec.information296 is not null then
1343 hr_utility.set_location('2nd rmr exists with val '||l_rbr_rec.information296,10);
1344 l_rate2_exists := 'Y';
1345 else
1346 l_rate2_exists := 'N';
1347 end if;
1348 if l_rbr_rec.information162 is not null or
1349 l_rbr_rec.information25 is not null or
1350 l_rbr_rec.information297 is not null or
1351 l_rbr_rec.information298 is not null or
1352 l_rbr_rec.information299 is not null or
1353 l_rbr_rec.information300 is not null then
1354 hr_utility.set_location('3rd rmr exists with val '||l_rbr_rec.information300,10);
1355 l_rate3_exists := 'Y';
1356 else
1357 l_rate3_exists := 'N';
1358 end if;
1359 if l_rbr_rec.information169 is not null or
1360 l_rbr_rec.information30 is not null or
1361 l_rbr_rec.information301 is not null or
1362 l_rbr_rec.information302 is not null or
1363 l_rbr_rec.information303 is not null or
1364 l_rbr_rec.information304 is not null then
1365 hr_utility.set_location('4th rmr exists with val '||l_rbr_rec.information294,10);
1366 l_rate4_exists := 'Y';
1367 else
1368 l_rate4_exists := 'N';
1369 end if;
1370 if l_rate1_exists = 'Y' then
1371 hr_utility.set_location('update rmn status '||l_rbr_rec.information1,10);
1372 set_rmn_stat(p_rmn_cer_id => l_rbr_rec.information1);
1373 hr_utility.set_location('writing 1st rmr for rbr'||p_rbr_cer_id,10);
1374 write_rmr_row(p_rmr_cer_id => l_rbr_rec.information160,
1375 p_rmn_cer_id => l_rbr_rec.information1,
1376 p_rate_level => 0,
1377 p_cet_id => l_rbr_rec.copy_entity_txn_id,
1378 p_effective_date => p_effective_date,
1379 p_min => l_rbr_rec.information287,
1380 p_mid => l_rbr_rec.information288,
1381 p_max => l_rbr_rec.information289,
1382 p_value => l_rbr_rec.information290);
1383 if l_rate2_exists = 'Y' then
1384 hr_utility.set_location('writing 2nd rmr for rbr'||p_rbr_cer_id,10);
1385 write_rmr_row(p_rmr_cer_id => l_rbr_rec.information161,
1386 p_rmn_cer_id => l_rbr_rec.information1,
1387 p_rate_level => 1,
1388 p_cet_id => l_rbr_rec.copy_entity_txn_id,
1389 p_effective_date => p_effective_date,
1390 p_min => l_rbr_rec.information293,
1391 p_mid => l_rbr_rec.information294,
1392 p_max => l_rbr_rec.information295,
1393 p_value => l_rbr_rec.information296);
1394 if l_rate3_exists = 'Y' then
1395 hr_utility.set_location('writing 3rd rmr for rbr'||p_rbr_cer_id,10);
1396 write_rmr_row(p_rmr_cer_id => l_rbr_rec.information162,
1397 p_rmn_cer_id => l_rbr_rec.information1,
1398 p_rate_level => 2,
1399 p_cet_id => l_rbr_rec.copy_entity_txn_id,
1400 p_effective_date => p_effective_date,
1401 p_min => l_rbr_rec.information297,
1402 p_mid => l_rbr_rec.information298,
1403 p_max => l_rbr_rec.information299,
1404 p_value => l_rbr_rec.information300);
1405 if l_rate4_exists = 'Y' then
1406 hr_utility.set_location('writing 4th rmr for rbr'||p_rbr_cer_id,10);
1407 write_rmr_row(p_rmr_cer_id => l_rbr_rec.information169,
1408 p_rmn_cer_id => l_rbr_rec.information1,
1409 p_rate_level => 3,
1410 p_cet_id => l_rbr_rec.copy_entity_txn_id,
1411 p_effective_date => p_effective_date,
1412 p_min => l_rbr_rec.information301,
1413 p_mid => l_rbr_rec.information302,
1414 p_max => l_rbr_rec.information303,
1415 p_value => l_rbr_rec.information304);
1416 else
1417 hr_utility.set_location('4th rate not there',10);
1418 end if;
1419 else
1420 hr_utility.set_location('3rd rate not there',10);
1421 end if;
1422 else
1423 hr_utility.set_location('2nd rate not there',10);
1424 end if;
1425 else
1426 hr_utility.set_location('1st rate not there',10);
1427 end if;
1428 end rbr_writeback;
1429 procedure load_plan(p_rate_matrix_id in number,
1430 p_cet_id in number,
1431 p_effective_date in date,
1432 p_business_group_id in number,
1433 p_plan_cer_id out nocopy number) is
1434 l_pln_tr_id number;
1435 l_pln_tr_name varchar2(80);
1436 l_plan_cer_ovn number;
1437 l_plan_cer_id number;
1438
1439 l_rmn_tr_id number;
1440 l_rmn_tr_name varchar2(80);
1441 l_rmn_cer_ovn number;
1442 l_rmn_cer_id number;
1443
1444 l_rcr_tr_id number;
1445 l_rcr_tr_name varchar2(80);
1446 l_rcr_cer_ovn number;
1447 l_rcr_cer_id number;
1448
1449 l_rate_count number;
1450
1451 cursor c1 is select * from ben_pl_f
1452 where pl_id = p_rate_matrix_id
1453 and p_effective_date between effective_start_date and effective_end_date;
1454 begin
1455 for pl_row in c1 loop
1456 hr_utility.set_location('for copying matx plan row',10);
1457 pqh_gsp_hr_to_stage.get_table_route_details
1458 (p_table_alias => 'PLN',
1459 p_table_route_id => l_pln_tr_id,
1460 p_table_name => l_pln_tr_name);
1461 hr_utility.set_location('tr name is '||l_pln_tr_name,10);
1462
1463 pqh_gsp_hr_to_stage.get_table_route_details
1464 (p_table_alias => 'RMN',
1465 p_table_route_id => l_rmn_tr_id,
1466 p_table_name => l_rmn_tr_name);
1467 hr_utility.set_location('tr name is '||l_rmn_tr_name,10);
1468
1469 pqh_gsp_hr_to_stage.get_table_route_details
1470 (p_table_alias => 'RCR',
1471 p_table_route_id => l_rcr_tr_id,
1472 p_table_name => l_rcr_tr_name);
1473 hr_utility.set_location('tr name is '||l_rcr_tr_name,10);
1474
1475
1476 ben_copy_entity_results_api.create_copy_entity_results(
1477 p_effective_date => p_effective_date
1478 ,p_copy_entity_txn_id => p_cet_id
1479 ,p_result_type_cd => 'DISPLAY'
1480 ,p_table_name => l_pln_tr_name
1481 ,p_table_route_id => l_pln_tr_id
1482 ,p_table_alias => 'PLN'
1483 ,p_dml_operation => 'COPIED'
1484 ,p_information1 => pl_row.pl_id
1485 ,p_information2 => pl_row.effective_start_date
1486 ,p_information3 => pl_row.effective_end_date
1487 ,p_information4 => p_business_group_id
1488 ,p_information5 => pl_row.name
1489 ,p_information170 => pl_row.name
1490 ,p_information265 => pl_row.object_version_number
1491 ,p_information19 => pl_row.pl_stat_cd
1492 ,p_information93 => pl_row.short_code
1493 ,p_information94 => pl_row.short_name
1494 ,p_copy_entity_result_id => l_plan_cer_id
1495 ,p_object_version_number => l_plan_cer_ovn);
1496 hr_utility.set_location('pln cer row is '||l_plan_cer_id,10);
1497 p_plan_cer_id := l_plan_cer_id;
1498 if p_plan_cer_id is not null then
1499 matx_rates(p_rate_matrix_id => pl_row.pl_id,
1500 p_cet_id => p_cet_id,
1501 p_effective_date => p_effective_date,
1502 p_business_group_id => p_business_group_id,
1503 p_rcr_tr_name => l_rcr_tr_name,
1504 p_rcr_tr_id => l_rcr_tr_id,
1505 p_count => l_rate_count);
1506 hr_utility.set_location('rates counted and copied '||l_rate_count,50);
1507 end if;
1508 end loop;
1509 exception
1510 when others then
1511 hr_utility.set_location('issues in copying plan row',10);
1512 raise;
1513 end load_plan;
1514
1515 procedure node_val_details(p_rate_matrix_node_id in number,
1516 p_rmn_cer_id in number,
1517 p_cet_id in number,
1518 p_crit_type in varchar2,
1519 p_effective_date in date,
1520 p_business_group_id in number,
1521 p_rnv_tr_name in varchar2,
1522 p_rnv_tr_id in number) is
1523 l_rnv_cer_id number;
1524 l_rnv_cer_ovn number;
1525 l_number_oh_value number;
1526 l_number_ph_value number;
1527 l_number_xh_value number;
1528 l_number_on_value number;
1529 l_number_pn_value number;
1530 l_number_xn_value number;
1531 cursor c_node_val is select * from pqh_rt_matrix_node_values
1532 where rate_matrix_node_id = p_rate_matrix_node_id;
1533 begin
1534 hr_utility.set_location('inside copying node_val ',10);
1535 for node_val in c_node_val loop
1536 hr_utility.set_location('copying node_val to staging',20);
1537 if p_crit_type = 'OH' then
1538 l_number_oh_value := node_val.number_value1;
1539 l_number_on_value := node_val.number_value2;
1540 elsif p_crit_type = 'PH' then
1541 l_number_ph_value := node_val.number_value1;
1542 l_number_pn_value := node_val.number_value2;
1543 else
1544 l_number_xh_value := node_val.number_value1;
1545 l_number_xn_value := node_val.number_value2;
1546 end if;
1547 ben_copy_entity_results_api.create_copy_entity_results(
1548 p_effective_date => p_effective_date
1549 ,p_copy_entity_txn_id => p_cet_id
1550 ,p_result_type_cd => 'DISPLAY'
1551 ,p_table_name => p_rnv_tr_name
1552 ,p_table_route_id => p_rnv_tr_id
1553 ,p_table_alias => 'RMV'
1554 ,p_dml_operation => 'COPIED'
1555 ,p_information1 => node_val.node_value_id
1556 ,p_information4 => p_business_group_id
1557 ,p_information265 => node_val.object_version_number
1558 ,p_information161 => p_rate_matrix_node_id
1559 ,p_information13 => node_val.char_value1
1560 ,p_information14 => node_val.char_value2
1561 ,p_information169 => l_number_xh_value
1562 ,p_information174 => l_number_xn_value
1563 ,p_information223 => l_number_oh_value
1564 ,p_information224 => l_number_on_value
1565 ,p_information225 => l_number_ph_value
1566 ,p_information226 => l_number_pn_value
1567 ,p_information166 => node_val.date_value1
1568 ,p_information167 => node_val.date_value2
1569 ,p_information15 => node_val.char_value3
1570 ,p_information16 => node_val.char_value4
1571 ,p_information221 => node_val.number_value3
1572 ,p_information222 => node_val.number_value4
1573 ,p_information306 => node_val.date_value3
1574 ,p_information307 => node_val.date_value4
1575 ,p_information12 => node_val.short_code
1576 ,p_parent_entity_result_id => p_rmn_cer_id
1577 ,p_gs_parent_entity_result_id => p_rmn_cer_id
1578 ,p_copy_entity_result_id => l_rnv_cer_id
1579 ,p_object_version_number => l_rnv_cer_ovn);
1580 hr_utility.set_location('rnv cer row is '||l_rnv_cer_id,30);
1581 end loop;
1582 exception
1583 when others then
1584 hr_utility.set_location('issues in pulling node values',10);
1585 raise;
1586 end node_val_details;
1587 procedure node_rates(p_rate_matrix_node_id in number,
1588 p_rmn_cer_id in number,
1589 p_cet_id in number,
1590 p_effective_date in date,
1591 p_business_group_id in number,
1592 p_rmr_tr_name in varchar2,
1593 p_rmr_tr_id in number) is
1594 l_rmr_cer_id number;
1595 l_rmr_cer_ovn number;
1596 cursor c_node_rate is select * from pqh_rate_matrix_rates_f
1597 where rate_matrix_node_id = p_rate_matrix_node_id
1598 and p_effective_date between effective_start_date and effective_end_date;
1599 begin
1600 hr_utility.set_location('inside copying node_rate ',10);
1601 for node_rate in c_node_rate loop
1602 hr_utility.set_location('copying node_rate to staging',20);
1603 ben_copy_entity_results_api.create_copy_entity_results(
1604 p_effective_date => p_effective_date
1605 ,p_copy_entity_txn_id => p_cet_id
1606 ,p_result_type_cd => 'DISPLAY'
1607 ,p_table_name => p_rmr_tr_name
1608 ,p_table_route_id => p_rmr_tr_id
1609 ,p_table_alias => 'RMR'
1610 ,p_dml_operation => 'COPIED'
1611 ,p_information1 => node_rate.rate_matrix_rate_id
1612 ,p_information2 => node_rate.effective_start_date
1613 ,p_information3 => node_rate.effective_end_date
1614 ,p_information4 => p_business_group_id
1615 ,p_information265 => node_rate.object_version_number
1616 ,p_information294 => node_rate.min_rate_value
1617 ,p_information295 => node_rate.max_rate_value
1618 ,p_information296 => node_rate.mid_rate_value
1619 ,p_information297 => node_rate.rate_value
1620 ,p_information162 => node_rate.criteria_rate_defn_id
1621 ,p_information161 => p_rate_matrix_node_id
1622 ,p_parent_entity_result_id => p_rmn_cer_id
1623 ,p_copy_entity_result_id => l_rmr_cer_id
1624 ,p_object_version_number => l_rmr_cer_ovn);
1625 hr_utility.set_location('rmr cer row is '||l_rmr_cer_id,30);
1626 end loop;
1627 exception
1628 when others then
1629 hr_utility.set_location('issues in pulling node rates',10);
1630 raise;
1631 end node_rates;
1632 procedure load_matrix_dtls(p_rate_matrix_id in number,
1633 p_cet_id in number,
1634 p_plan_cer_id in number,
1635 p_effective_date in date,
1636 p_business_group_id in number,
1637 p_status_flag out nocopy number) is
1638 l_rmn_tr_id number;
1639 l_rmn_tr_name varchar2(80);
1640 l_rmn_cer_id number;
1641 l_rmn_cer_ovn number;
1642
1643 l_rmr_tr_id number;
1644 l_rmr_tr_name varchar2(80);
1645
1646 l_rnv_tr_id number;
1647 l_rnv_tr_name varchar2(80);
1648 l_node_name varchar2(2000);
1649
1650 l_crt_tr_id number;
1651 l_crit_type varchar2(30);
1652 l_crt_tr_name varchar2(80);
1653 l_crit ben_eligy_criteria%rowtype;
1654 l_crt_cer_id number;
1655 l_crt_cer_ovn number;
1656 l_status_flag number := 0;
1657 l_parent_id number;
1658 /**
1659 cursor c_crit is select distinct criteria_short_code,level_number
1660 from pqh_rate_matrix_nodes
1661 where pl_id = p_rate_matrix_id
1662 and criteria_short_code is not null
1663 order by level_number;
1664 **/
1665 cursor c_crit is select distinct a.criteria_short_code,a.level_number
1666 from pqh_rate_matrix_nodes a, ben_eligy_prfl_f b
1667 where a.pl_id = p_rate_matrix_id
1668 and a.criteria_short_code is not null
1669 and a.eligy_prfl_id = b.eligy_prfl_id
1670 and p_effective_date between b.effective_start_date and b.effective_end_date
1671 order by a.level_number;
1672 --
1673 /**
1674 cursor c_node is select * from pqh_rate_matrix_nodes
1675 where pl_id = p_rate_matrix_id
1676 order by level_number;
1677 **/
1678 cursor c_node is select a.* from pqh_rate_matrix_nodes a
1679 where a.pl_id = p_rate_matrix_id
1680 and (a.eligy_prfl_id is null
1681 or exists (Select 1 from ben_eligy_prfl_f b
1682 where a.eligy_prfl_id = b.eligy_prfl_id
1683 and p_effective_date between b.effective_start_date and b.effective_end_date))
1684 order by level_number;
1685
1686 --
1687 begin
1688 hr_utility.set_location('inside plan dtl copy ',10);
1689 pqh_gsp_hr_to_stage.get_table_route_details
1690 (p_table_alias => 'RBC_CRIT',
1691 p_table_route_id => l_crt_tr_id,
1692 p_table_name => l_crt_tr_name);
1693 hr_utility.set_location('rbc_crit tr name is '||l_crt_tr_name,10);
1694
1695 pqh_gsp_hr_to_stage.get_table_route_details
1696 (p_table_alias => 'RMN',
1697 p_table_route_id => l_rmn_tr_id,
1698 p_table_name => l_rmn_tr_name);
1699 hr_utility.set_location('rmn tr name is '||l_rmn_tr_name,10);
1700
1701 pqh_gsp_hr_to_stage.get_table_route_details
1702 (p_table_alias => 'RMV',
1703 p_table_route_id => l_rnv_tr_id,
1704 p_table_name => l_rnv_tr_name);
1705 hr_utility.set_location('rnv tr name is '||l_rnv_tr_name,10);
1706
1707 pqh_gsp_hr_to_stage.get_table_route_details
1708 (p_table_alias => 'RMR',
1709 p_table_route_id => l_rmr_tr_id,
1710 p_table_name => l_rmr_tr_name);
1711 hr_utility.set_location('rmr tr name is '||l_rmr_tr_name,10);
1712
1713 begin
1714 for crit in c_crit loop
1715 hr_utility.set_location('for copying matx crit row',10);
1716 if crit.criteria_short_code is not null then
1717 get_criteria(p_criteria_short_code => crit.criteria_short_code,
1718 p_business_group_id => p_business_group_id,
1719 p_crit_rec => l_crit);
1720 end if;
1721 ben_copy_entity_results_api.create_copy_entity_results(
1722 p_effective_date => p_effective_date
1723 ,p_copy_entity_txn_id => p_cet_id
1724 ,p_result_type_cd => 'DISPLAY'
1725 ,p_table_name => l_crt_tr_name
1726 ,p_table_route_id => l_crt_tr_id
1727 ,p_table_alias => 'RBC_CRIT'
1728 ,p_dml_operation => 'COPIED'
1729 ,p_information4 => p_business_group_id
1730 ,p_information1 => l_crit.eligy_criteria_id
1731 ,p_information261 => p_rate_matrix_id
1732 ,p_information13 => crit.criteria_short_code
1733 ,p_information160 => crit.level_number
1734 ,p_information170 => l_crit.name
1735 ,p_information5 => l_crit.name
1736 ,p_parent_entity_result_id => p_plan_cer_id
1737 ,p_gs_parent_entity_result_id => nvl(l_crt_cer_id,p_plan_cer_id)
1738 ,p_copy_entity_result_id => l_crt_cer_id
1739 ,p_object_version_number => l_crt_cer_ovn);
1740 hr_utility.set_location('crt cer row is '||l_crt_cer_id,10);
1741 end loop;
1742 exception
1743 when others then
1744 hr_utility.set_location('issues in copying rbc_crit row',10);
1745 raise;
1746 end;
1747 for node in c_node loop
1748 hr_utility.set_location('for copying matx plan row',10);
1749 if node.level_number = 1 then
1750 hr_utility.set_location('root node, no need to build name',10);
1751 else
1752 l_node_name := build_rate_node_name
1753 (p_rate_matrix_node_id => node.rate_matrix_node_id,
1754 p_business_group_id => p_business_group_id,
1755 p_node_short_code => node.criteria_short_code,
1756 p_effective_date => p_effective_date);
1757 end if;
1758 if node.level_number > 1 then
1759 l_parent_id := get_rmn_cer(p_rmn_id => node.parent_node_id,
1760 p_cet_id => p_cet_id);
1761 else
1762 l_parent_id := p_plan_cer_id;
1763 end if;
1764 ben_copy_entity_results_api.create_copy_entity_results(
1765 p_effective_date => p_effective_date
1766 ,p_copy_entity_txn_id => p_cet_id
1767 ,p_result_type_cd => 'DISPLAY'
1768 ,p_table_name => l_rmn_tr_name
1769 ,p_table_route_id => l_rmn_tr_id
1770 ,p_table_alias => 'RMN'
1771 ,p_dml_operation => 'COPIED'
1772 ,p_information1 => node.rate_matrix_node_id
1773 ,p_information4 => p_business_group_id
1774 ,p_information265 => node.object_version_number
1775 ,p_information219 => l_node_name
1776 ,p_information5 => l_node_name
1777 ,p_information261 => node.pl_id
1778 ,p_information160 => node.level_number
1779 ,p_information12 => node.short_code
1780 ,p_information13 => node.criteria_short_code
1781 ,p_information161 => node.parent_node_id
1782 ,p_information169 => node.eligy_prfl_id
1783 ,p_parent_entity_result_id => p_plan_cer_id
1784 ,p_gs_parent_entity_result_id => l_parent_id
1785 ,p_copy_entity_result_id => l_rmn_cer_id
1786 ,p_object_version_number => l_rmn_cer_ovn);
1787 hr_utility.set_location('rmn cer row is '||l_rmn_cer_id,10);
1788 if node.criteria_short_code is not null then
1789 l_crit_type := get_crit_type(node.criteria_short_code,p_business_group_id);
1790 hr_utility.set_location('crit type is '||l_crit_type,10);
1791 if l_rmn_cer_id is not null then
1792 node_val_details(p_rate_matrix_node_id => node.rate_matrix_node_id,
1793 p_rmn_cer_id => l_rmn_cer_id,
1794 p_cet_id => p_cet_id,
1795 p_crit_type => l_crit_type,
1796 p_effective_date => p_effective_date,
1797 p_business_group_id => p_business_group_id,
1798 p_rnv_tr_name => l_rnv_tr_name,
1799 p_rnv_tr_id => l_rnv_tr_id);
1800 node_rates(p_rate_matrix_node_id => node.rate_matrix_node_id,
1801 p_rmn_cer_id => l_rmn_cer_id,
1802 p_cet_id => p_cet_id,
1803 p_effective_date => p_effective_date,
1804 p_business_group_id => p_business_group_id,
1805 p_rmr_tr_name => l_rmr_tr_name,
1806 p_rmr_tr_id => l_rmr_tr_id);
1807 else
1808 hr_utility.set_location('xxxxxxxxxxxxxxxxx',32);
1809 end if;
1810 else
1811 hr_utility.set_location('for top node ',40);
1812 end if;
1813 end loop;
1814 p_status_flag := l_status_flag;
1815 exception
1816 when others then
1817 hr_utility.set_location('issues in copying rmn row',10);
1818 raise;
1819 end load_matrix_dtls;
1820
1821 procedure load_matrix(p_rate_matrix_id in number,
1822 p_copy_entity_txn_id in number,
1823 p_effective_date in date,
1824 p_business_group_id in number,
1825 p_status_flag out nocopy number) is
1826 l_status_flag number := 0;
1827 l_plan_cer_id number;
1828 begin
1829 hr_utility.set_location('here to copy plan',10);
1830 load_plan(p_rate_matrix_id => p_rate_matrix_id,
1831 p_cet_id => p_copy_entity_txn_id,
1832 p_effective_date => p_effective_date,
1833 p_business_group_id => p_business_group_id,
1834 p_plan_cer_id => l_plan_cer_id);
1835 hr_utility.set_location('copied plan cer '||l_plan_cer_id,15);
1836 hr_utility.set_location('here to copy plan details',20);
1837 load_matrix_dtls(p_rate_matrix_id => p_rate_matrix_id,
1838 p_cet_id => p_copy_entity_txn_id,
1839 p_plan_cer_id => l_plan_cer_id,
1840 p_effective_date => p_effective_date,
1841 p_business_group_id => p_business_group_id,
1842 p_status_flag => l_status_flag);
1843 p_status_flag := l_status_flag;
1844 hr_utility.set_location('plan dtls copied with stat '||l_status_flag,25);
1845 end load_matrix;
1846 procedure chk_matrix_presence(p_rate_matrix_id in number,
1847 p_business_group_id in number,
1848 p_copy_entity_txn_id out nocopy number) is
1849 l_cet_id number;
1850 begin
1851 select txn.copy_entity_txn_id
1852 into l_cet_id
1853 from pqh_copy_entity_txns txn , ben_copy_entity_results cer
1854 where cer.copy_entity_txn_id = txn.copy_entity_txn_id
1855 and txn.context ='RBC_MATRIX'
1856 and txn.status = 'UPDATE'
1857 and txn.context_business_group_id = p_business_group_id
1858 and cer.table_alias = 'PLN'
1859 and cer.information1 = p_rate_matrix_id;
1860 hr_utility.set_location('cet row found is '||l_cet_id,10);
1861 p_copy_entity_txn_id := l_cet_id;
1862 exception
1863 when no_data_found then
1864 p_copy_entity_txn_id := l_cet_id;
1865 hr_utility.set_location('no cet row exists ',10);
1866 when others then
1867 hr_utility.set_location('issues in checking matx presence',10);
1868 raise;
1869 end chk_matrix_presence;
1870 procedure upd_matrix(p_rate_matrix_id in number,
1871 p_effective_date in date,
1872 p_business_group_id in number,
1873 p_mode in varchar2 default 'NORMAL',
1874 p_matrix_loaded out nocopy varchar2,
1875 p_copy_entity_txn_id out nocopy number) is
1876 l_cet_id number;
1877 l_status_flag number := 0;
1878 begin
1879 p_matrix_loaded := 'ERR';
1880 set_session_date(p_effective_date => p_effective_date);
1881 if p_mode ='NORMAL' then
1882 hr_utility.set_location('checking matx presence in staging',10);
1883 chk_matrix_presence(p_rate_matrix_id => p_rate_matrix_id,
1884 p_business_group_id => p_business_group_id,
1885 p_copy_entity_txn_id => l_cet_id);
1886 if l_cet_id is null then
1887 hr_utility.set_location('matx not in stage, copying ',10);
1888 create_matrix_txn(p_mode => 'UPDATE',
1889 p_business_group_id => p_business_group_id,
1890 p_effective_date => p_effective_date,
1891 p_copy_entity_txn_id => l_cet_id);
1892 hr_utility.set_location('cet row created '||l_cet_id,10);
1893 load_matrix(p_rate_matrix_id => p_rate_matrix_id,
1894 p_copy_entity_txn_id => l_cet_id,
1895 p_business_group_id => p_business_group_id,
1896 p_effective_date => p_effective_date,
1897 p_status_flag => l_status_flag );
1898 if l_status_flag = 0 then
1899 hr_utility.set_location('matrix copied ',10);
1900 p_matrix_loaded := 'YES';
1901 p_copy_entity_txn_id := l_cet_id;
1902 else
1903 hr_utility.set_location('matrix copy error ',10);
1904 p_matrix_loaded := 'ERR';
1905 end if;
1906 else
1907 p_copy_entity_txn_id := l_cet_id;
1908 p_matrix_loaded := 'NO';
1909 end if;
1910 elsif p_mode ='OVERRIDE' then
1911 hr_utility.set_location('override matrix in stage',100);
1912 hr_utility.set_location('checking matx presence in staging',10);
1913 chk_matrix_presence(p_rate_matrix_id => p_rate_matrix_id,
1914 p_business_group_id => p_business_group_id,
1915 p_copy_entity_txn_id => l_cet_id);
1916 if l_cet_id is null then
1917 hr_utility.set_location('matx doesnot exist',10);
1918 create_matrix_txn(p_mode => 'UPDATE',
1919 p_business_group_id => p_business_group_id,
1920 p_effective_date => p_effective_date,
1921 p_copy_entity_txn_id => l_cet_id);
1922 hr_utility.set_location('new cet row created '||l_cet_id,10);
1923 else
1924 delete_matrix(p_copy_entity_txn_id => l_cet_id);
1925 hr_utility.set_location('cer rows for cet deleted',100);
1926 end if;
1927 load_matrix(p_rate_matrix_id => p_rate_matrix_id,
1928 p_copy_entity_txn_id => l_cet_id,
1929 p_business_group_id => p_business_group_id,
1930 p_effective_date => p_effective_date,
1931 p_status_flag => l_status_flag );
1932 if l_status_flag = 0 then
1933 hr_utility.set_location('matrix copied ',10);
1934 p_matrix_loaded := 'YES';
1935 p_copy_entity_txn_id := l_cet_id;
1936 else
1937 hr_utility.set_location('matrix copy error ',10);
1938 p_matrix_loaded := 'ERR';
1939 end if;
1940 else
1941 hr_utility.set_location('wrong mode passed',100);
1942 end if;
1943 end upd_matrix;
1944 procedure cre_matrix(p_business_group_id in number,
1945 p_effective_date in date,
1946 p_copy_entity_txn_id out nocopy number) is
1947 l_cet_id number;
1948 l_cer_id number;
1949 l_cer_ovn number;
1950 begin
1951 hr_utility.set_location('creating cet row',10);
1952 create_matrix_txn(p_mode => 'CREATE',
1953 p_business_group_id => p_business_group_id,
1954 p_effective_date => p_effective_date,
1955 p_copy_entity_txn_id => l_cet_id);
1956 if l_cet_id is not null then
1957 hr_utility.set_location('populate out params',10);
1958 p_copy_entity_txn_id := l_cet_id;
1959 else
1960 hr_utility.set_location('cet row is not there',10);
1961 end if;
1962 end cre_matrix;
1963 end pqh_rbc_stage;