[Home] [Help]
PACKAGE BODY: APPS.PQH_RBC_STAGE_TO_RBC
Source
1 PACKAGE BODY PQH_RBC_STAGE_TO_RBC as
2 /* $Header: pqrbcsrb.pkb 120.4 2006/02/20 16:06 srajakum noship $ */
3
4 g_package Varchar2(30) := 'pqh_rbc_stage_to_rbc';
5 g_pln_short_code Varchar2(1000) := null;
6
7 function get_parent_rmn(p_copy_entity_txn_id in number,
8 p_copy_entity_result_id in number)
9 return number
10 is
11
12 l_parent_id number;
13 Begin
14 hr_utility.set_location('Into get_parent_rmn',210);
15
16 hr_utility.set_location('Into get_parent_rmn copy_entity_txn_id'||p_copy_entity_txn_id,210);
17 hr_utility.set_location('Into get_parent_rmn copy_entity_result_id'||p_copy_entity_result_id,210);
18
19 select information1
20 into l_parent_id
21 from ben_copy_entity_results
22 where copy_entity_txn_id = p_copy_entity_txn_id
23 and copy_entity_result_id = p_copy_entity_result_id
24 and table_alias = 'RMN';
25
26 hr_utility.set_location('Into get_parent_rmn parent_id'||l_parent_id,210);
27
28 hr_utility.set_location('leaving get_parent_rmn',1);
29 return l_parent_id;
30
31 exception
32 when no_data_found then
33 hr_utility.set_location('Into get_parent_rmn exception',210);
34 return null;
35
36 End get_parent_rmn;
37
38 Function chk_acty_base_rate_exist (
39 p_pl_id in number,
40 p_effective_date in date,
41 p_business_group_id in number,
42 P_MAPPING_TABLE_PK_ID in number
43 ) return varchar2 is
44 cursor base_rt_cur is
45 Select *
46 from ben_acty_base_rt_f
47 where pl_id = p_pl_id
48 and business_group_id = p_business_group_id
49 and mapping_table_pk_id = p_mapping_table_pk_id
50 and p_effective_date between effective_start_date and effective_end_date;
51
52 l_status varchar2(1) := 'N';
53 l_base_rt_rec base_rt_cur%rowtype;
54 Begin
55 open base_rt_cur;
56 loop
57 fetch base_rt_cur into l_base_rt_rec;
58 exit when base_rt_cur%notfound;
59 if base_rt_cur%found then
60 l_status := 'Y';
61 end if;
62 end loop;
63 return l_status;
64 End chk_acty_base_rate_exist;
65
66
67 procedure get_elig_det_for_rmn (p_business_group_id in number,
68 p_rmn_id in number,
69 p_elig_prfl_id out nocopy number,
70 p_criteria_short_code out nocopy varchar2)
71 is
72 Begin
73 hr_utility.set_location('Entering get_elig_det_for_rmn',1);
74
75 select CRITERIA_SHORT_CODE, ELIGY_PRFL_ID
76 into p_criteria_short_code, p_elig_prfl_id
77 from pqh_rate_matrix_nodes
78 where rate_matrix_node_id = p_rmn_id
79 and business_group_id = p_business_group_id;
80
81 hr_utility.set_location('leaving get_elig_det_for_rmn',1);
82 End get_elig_det_for_rmn;
83
84 procedure rmn_writeback(p_copy_entity_result_id in number,
85 p_rmn_id in number,
86 p_copy_entity_txn_id in number) is
87
88 Begin
89 hr_utility.set_location('inside rmn_writeback ',210);
90
91 hr_utility.set_location('writing back on ben_copy_entity results ',210);
92 hr_utility.set_location('writing back parent_id '||p_rmn_id,210);
93
94 update ben_copy_entity_results
95 set information1 = p_rmn_id
96 where COPY_ENTITY_RESULT_ID = p_copy_entity_result_id
97 and copy_entity_txn_id = p_copy_entity_txn_id;
98
99 update ben_copy_entity_results
100 set information161 = p_rmn_id
101 where GS_PARENT_ENTITY_RESULT_ID = p_copy_entity_result_id
102 and copy_entity_txn_id = p_copy_entity_txn_id
103 and table_alias in ('RMV');
104
105 update ben_copy_entity_results
106 set information161 = p_rmn_id
107 where PARENT_ENTITY_RESULT_ID = p_copy_entity_result_id
108 and copy_entity_txn_id = p_copy_entity_txn_id
109 and table_alias in ('RMR');
110
111 hr_utility.set_location('leaving rmn_writeback ',210);
112 End rmn_writeback;
113
114
115 function get_pl_typ_name return varchar2 is
116 l_proc varchar2(72) := g_package||'get_pl_typ_name';
117 l_name varchar2(80) ;
118 begin
119 hr_utility.set_location(' Inside '||l_proc,210);
120
121 select meaning into l_name
122 from hr_lookups
123 where lookup_type = 'PQH_GSP_LE_PT_NAME'
124 and lookup_code = 'RBC_PT';
125
126 hr_utility.set_location(' Leaving '||l_proc,210);
127 return l_name ;
128 exception
129 when others then
130 hr_utility.set_location('issue in lookup ',10);
131 raise;
132 end get_pl_typ_name;
133
134 function get_short_code (p_table_alias in varchar2)return varchar2 is
135
136 l_sql varchar2(2000);
137 l_code varchar2(1000);
138 l_seq varchar2(1000);
139 l_seq_no number;
140
141 begin
142 hr_utility.set_location(' Inside get_short_code ',210);
143
144 if p_table_alias = 'RMN' then
145 l_seq := 'PQH_RATE_MATRIX_NODES_S.NEXTVAL';
146 elsif p_table_alias = 'RMV' then
147 l_seq := 'PQH_RT_MATRIX_NODE_VALUES_S.NEXTVAL';
148 elsif p_table_alias = 'RMR' then
149 l_seq := 'PQH_RATE_MATRIX_RATES_S.NEXTVAL';
150 end if;
151
152 l_code := g_pln_short_code;
153
154 l_sql := 'select '||l_seq||' from dual';
155
156 hr_utility.set_location('l_sql is '||substr(l_sql,1,50),20);
157 hr_utility.set_location('l_sql is '||substr(l_sql,51,50),20);
158 hr_utility.set_location('l_sql is '||substr(l_sql,101,50),20);
159
160 EXECUTE IMMEDIATE l_sql
161 INTO l_seq_no;
162
163 l_code := l_code||to_char(l_seq_no);
164
165 hr_utility.set_location('For '||p_table_alias||' short_code is '||l_code,210);
166
167 hr_utility.set_location(' Leaving get_short_code ',210);
168
169 return l_code;
170
171 exception
172 when no_data_found then
173 raise;
174
175 end get_short_code;
176
177 function create_plan_type (p_business_group_id in number
178 ,p_copy_entity_txn_id in number
179 ,p_name in varchar2)
180 return number is
181 l_proc varchar2(72) := g_package||'create_plan_type';
182 l_start_of_time DATE:= to_date('01-01-1951','DD-MM-YYYY');
183 l_pl_typ_id number;
184 l_effective_start_date date;
185 l_effective_end_date date;
186 l_object_version_number number;
187 begin
188 hr_utility.set_location('Entering:'|| l_proc, 10);
189
190 ben_plan_type_api.create_plan_type(
191 p_pl_typ_id => l_pl_typ_id
192 ,p_effective_start_date => l_effective_start_date
193 ,p_effective_end_date => l_effective_end_date
194 ,p_object_version_number => l_object_version_number
195 ,p_effective_date => l_start_of_time
196 ,p_name => p_name
197 ,p_business_group_id => p_business_group_id
198 ,p_opt_typ_cd => 'RBC'
199 ,p_pl_typ_stat_cd => 'A'
200 ,p_no_mx_enrl_num_dfnd_flag => 'N'
201 ,p_no_mn_enrl_num_dfnd_flag => 'N'
202 );
203
204 hr_utility.set_location('Leaving:'|| l_proc, 10);
205
206 return l_pl_typ_id;
207 end create_plan_type;
208 --
209 function get_rbc_plntyp_str_date (p_business_group_id in number
210 ,p_copy_entity_txn_id in number default null
211 )
212 return date is
213 l_proc varchar2(72) := g_package||'get_rbc_plntyp_str_date';
214 l_plan_type_date DATE := null;
215 begin
216 hr_utility.set_location('Entering:'|| l_proc, 10);
217 select min(effective_start_date)
218 into l_plan_type_date
219 from ben_pl_typ_f
220 where business_group_id = p_business_group_id
221 and opt_typ_cd ='RBC'
222 and pl_typ_stat_cd ='A';
223 hr_utility.set_location('Plan Type date is :'|| l_plan_type_date, 20);
224 return l_plan_type_date ;
225 exception
226 when others then
227 hr_utility.set_location('Problem in determining Plan Type date ',40);
228 raise;
229 end get_rbc_plntyp_str_date ;
230 --
231 procedure setup_check(p_copy_entity_txn_id in number
232 ,p_effective_date in date
233 ,p_business_group_id in number
234 ,p_status out nocopy varchar2
235 ) is
236 l_status varchar2(30) ;
237 l_ler_id number;
238 l_ler_name varchar2(240);
239 l_pt_id number;
240 l_pt_name varchar2(240);
241 l_start_of_time DATE:= get_rbc_plntyp_str_date(p_business_group_id,p_copy_entity_txn_id);
242 l_effective_start_date date ;
243 l_pl_typ_name varchar2(80) ;
244
245 begin
246
247 hr_utility.set_location('Entering: setup_check', 10);
248 if l_status is null then
249 begin
250 select pl_typ_id,name,effective_start_date
251 into l_pt_id,l_pt_name,l_effective_start_date
252 from ben_pl_typ_f
253 where trunc(effective_end_date) = hr_general.end_of_time
254 and business_group_id = p_business_group_id
255 and opt_typ_cd ='RBC';
256
257 hr_utility.set_location('pl_typ name '||substr(l_pt_name,1,40),10);
258
259 if l_effective_start_date <> l_start_of_time then
260 l_status := 'WRONG-DATE-PT' ;
261 end if ;
262
263 exception
264 when no_data_found then
265 hr_utility.set_location('No PT of RBC ',20);
266 l_pl_typ_name := get_pl_typ_name();
267 l_pt_id := create_plan_type (p_business_group_id,p_copy_entity_txn_id,l_pl_typ_name);
268 when too_many_rows then
269 hr_utility.set_location('many PT of RBC ',20);
270 l_status := 'MANY-PT';
271 when others then
272 hr_utility.set_location('issue in Getting RBC PT ',20);
273 l_status := 'PT-ERR';
274 end;
275 end if;
276
277 if l_status is null then
278 hr_utility.set_location('setup is fine, update staging area',10);
279 -- p_plan_tp_created_flag := l_plan_tp_created_flag;
280 p_status := 'Y';
281 else
282 p_status := l_status;
283 hr_utility.set_location('control goes back with status'||l_status,10);
284 end if;
285
286 hr_utility.set_location('Leaving: setup_check', 10);
287
288 end setup_check;
289
290
291
292 procedure Delete_RMR(p_copy_entity_txn_id in number,
293 p_effective_date in date,
294 p_business_group_id in number,
295 p_Date_Track_Mode in Varchar2) is
296
297 cursor del_rmr is
298 select *
299 from ben_copy_entity_results
300 where copy_entity_txn_id = p_copy_entity_txn_id
301 and table_alias = 'RMR'
302 and dml_operation = 'DELETE';
303
304 l_ovn number;
305 l_effective_start_date date;
306 l_effective_end_date date;
307 Begin
308
309 hr_utility.set_location('Entering: Delete_RMR', 10);
310
311 for del_rec in del_rmr loop
312 l_ovn := del_rec.Information265;
313
314 PQH_RATE_MATRIX_RATES_API.delete_rate_matrix_rate
315 (p_rate_matrix_rate_ID => del_rec.Information1
316 ,p_effective_start_date => l_effective_start_date
317 ,p_effective_end_date => l_effective_end_date
318 ,p_object_version_number => l_ovn
319 ,p_effective_date => p_effective_date
320 ,p_datetrack_mode => p_Date_Track_Mode);
321
322 end loop;
323
324 hr_utility.set_location('Leaving: Delete_RMR', 10);
325 End Delete_RMR;
326
327 procedure Delete_RMV(p_copy_entity_txn_id in number,
328 p_effective_date in date,
329 p_business_group_id in number) is
330
331 cursor del_rmv is
332 select *
333 from ben_copy_entity_results
334 where copy_entity_txn_id = p_copy_entity_txn_id
335 and table_alias = 'RMV'
336 and dml_operation = 'DELETE';
337 Begin
338
339 hr_utility.set_location('Entering: Delete_RMV', 10);
340
341 for del_rec in del_rmv loop
342 PQH_RT_MATRIX_NODE_VALUES_API.delete_rt_matrix_node_value
343 (p_effective_date => p_effective_date
344 ,p_NODE_VALUE_ID => Del_rec.information1
345 ,p_object_version_number => del_rec.information265
346 );
347
348 /* pqh_rbc_elpro.delete_criteria
349 (
350
351 )*/
352
353 end loop;
354
355 hr_utility.set_location('Leaving: Delete_RMV', 10);
356 End Delete_RMV;
357
358 procedure Delete_RMN(p_copy_entity_txn_id in number,
359 p_effective_date in date,
360 p_business_group_id in number) is
361
362 cursor del_rmn is
363 select *
364 from ben_copy_entity_results
365 where copy_entity_txn_id = p_copy_entity_txn_id
366 and table_alias = 'RMN'
367 and dml_operation = 'DELETE';
368
369 Begin
370
371 hr_utility.set_location('Entering: Delete_RMN', 10);
372 for del_rec in del_rmn loop
373
374 PQH_RATE_MATRIX_NODES_API.delete_rate_matrix_node
375 (p_effective_date => p_effective_date
376 ,p_rate_matrix_node_id => del_rec.information1
377 ,p_object_version_number => del_rec.information265
378 );
379
380
381 end loop;
382
383 hr_utility.set_location('Entering: Delete_RMN', 10);
384
385 End Delete_RMN;
386
387 procedure Delete_plan(p_copy_entity_txn_id in number,
388 p_effective_date in date,
389 p_business_group_id in number,
390 p_Date_Track_Mode in varchar2) is
391
392 cursor del_plan is
393 select *
394 from ben_copy_entity_results
395 where copy_entity_txn_id = p_copy_entity_txn_id
396 and table_alias = 'PLN'
397 and dml_operation = 'DELETE';
398
399 l_ovn number;
400 l_effective_start_date date;
401 l_effective_end_date date;
402
403 Begin
404 hr_utility.set_location('inside delete_plan',1);
405 for del_rec in del_plan loop
406 l_ovn := del_rec.information265;
407
408 ben_Plan_api.delete_Plan
409 (p_pl_id => del_rec.information1
410 ,p_effective_start_date => l_effective_start_date
411 ,p_effective_end_date => l_effective_end_date
412 ,p_object_version_number => l_ovn
413 ,p_effective_date => p_effective_date
414 ,p_datetrack_mode => p_Date_Track_Mode
415 );
416
417
418 end loop;
419 hr_utility.set_location('leaving delete_plan',1);
420 End Delete_plan;
421
422
423 procedure pre_push_data(p_copy_entity_txn_id in number,
424 p_effective_date in date,
425 p_business_group_id in number,
426 p_Date_Track_Mode in Varchar2,
427 p_status out nocopy varchar2) is
428
429 l_return varchar2(30) := 'YES';
430 l_effective_date date;
431 l_Del_Dt_Mode Varchar2(30);
432 l_status varchar2(30);
433
434 begin
435 hr_utility.set_location('inside pre-push',1);
436
437 If P_Date_Track_Mode = 'UPDATE_OVERRIDE' Then
438 l_Del_Dt_Mode := 'DELETE';
439 Else
440 l_Del_Dt_Mode := 'ZAP';
441 End If;
442
443 Delete_RMR(p_copy_entity_txn_id => p_copy_entity_txn_id,
444 p_effective_date => p_effective_date,
445 p_business_group_id => p_business_group_id,
446 p_Date_Track_Mode => 'ZAP');
447
448 Delete_RMV(p_copy_entity_txn_id => p_copy_entity_txn_id,
449 p_effective_date => p_effective_date,
450 p_business_group_id => p_business_group_id);
451
452 Delete_RMN(p_copy_entity_txn_id => p_copy_entity_txn_id,
453 p_effective_date => p_effective_date,
454 p_business_group_id => p_business_group_id);
455
456
457 Delete_plan(p_copy_entity_txn_id => p_copy_entity_txn_id,
458 p_effective_date => p_effective_date,
459 p_business_group_id => p_business_group_id,
460 p_Date_Track_Mode => l_Del_Dt_Mode);
461
462 setup_check(p_copy_entity_txn_id => p_copy_entity_txn_id,
463 p_effective_date => p_effective_date,
464 p_business_group_id => p_business_group_id,
465 p_status => l_status);
466
467 if l_status <> 'Y' then
468 if l_status = 'WRONG-DATE-PT' then
469 hr_utility.set_message(8302,'PQH_RBC_WRONG_ST_DT_PT');
470 hr_multi_message.add;
471 elsif l_status = 'MANY-PT' then
472 hr_utility.set_message(8302,'PQH_RBC_MANY_PT');
473 hr_multi_message.add;
474 end if;
475 l_return := 'NO';
476 end if;
477
478 p_status := l_return;
479 hr_utility.set_location('leaving pre-push',100);
480
481 exception
482 when others then
483 l_return := 'NO';
484 p_status := l_return;
485 raise;
486 end pre_push_data;
487
488
489 Procedure stage_to_rmn_values(p_copy_entity_txn_id in number,
490 p_business_group_id in number,
491 p_effective_date in date,
492 p_rmn_id in number )
493 is
494 l_proc varchar2(61) :='stage_to_rmn_values';
495 l_rmv_id number;
496 l_seq number;
497 l_db_ovn number;
498 l_ovn number;
499 l_object varchar2(80);
500 l_message_text varchar2(2000);
501 l_short_code varchar2(1000);
502 l_number_value1 number;
503 l_number_value2 number;
504 l_criteria_short_code varchar2(80);
505 l_elig_prfl_id number;
506 cursor csr_rmv is
507 select *
508 from ben_copy_entity_results
509 where copy_entity_txn_id = p_copy_entity_txn_id
510 and table_alias = 'RMV'
511 and dml_operation in ('CREATE','UPDATE')
512 and information161 = p_rmn_id;
513 begin
514
515 hr_utility.set_location('inside'||l_proc,10);
516
517 for rmv_rec in csr_rmv loop
518 l_ovn := rmv_rec.Information265;
519 l_rmv_id := rmv_rec.Information1;
520 if rmv_rec.dml_operation = 'CREATE' then
521 hr_utility.set_location('new node value is being created'||l_proc,20);
522 l_short_code := rmv_rec.INFORMATION12;
523 hr_utility.set_location('Short_code is '||l_short_code,20);
524 /* Short code genration for the rows witout short code */
525
526 if l_short_code is null then
527 hr_utility.set_location('Short_code is null',20);
528 l_short_code := get_short_code('RMV');
529 hr_utility.set_location('Short_code generated'||l_short_code,20);
530 end if;
531 /*
532
533 Assigning number values depending upon the criteria selected.
534 if organization hierarchy id (information223 ) is not null then
535 number_value1 = information223
536 number_value2 = information224
537 if position hierarchy id (information225 ) is not null then
538 number_value1 = information225
539 number_value2 = information226
540 else
541 number_value1 = information169
542 number_value2 = information174
543
544 */
545 if rmv_rec.information223 is not null then
546 l_number_value1 := rmv_rec.information223;
547 l_number_value2 := rmv_rec.information224;
548 elsif rmv_rec.information225 is not null then
549 l_number_value1 := rmv_rec.information225;
550 l_number_value2 := rmv_rec.information226;
551 else
552 l_number_value1 := rmv_rec.information169;
553 l_number_value2 := rmv_rec.information174;
554 end if;
555
556 begin
557 hr_utility.set_location('Node value id'||l_rmv_id,20);
558 PQH_RT_MATRIX_NODE_VALUES_API.create_rt_matrix_node_value
559 (p_effective_date => p_effective_date
560 ,p_NODE_VALUE_ID => l_rmv_id
561 ,p_RATE_MATRIX_NODE_ID => rmv_rec.INFORMATION161
562 ,p_SHORT_CODE => l_short_code
563 ,p_CHAR_VALUE1 => rmv_rec.INFORMATION13
564 ,p_CHAR_VALUE2 => rmv_rec.INFORMATION14
565 ,p_CHAR_VALUE3 => rmv_rec.INFORMATION15
566 ,p_CHAR_VALUE4 => rmv_rec.INFORMATION16
567 ,p_NUMBER_VALUE1 => l_number_value1
568 ,p_NUMBER_VALUE2 => l_number_value2
569 ,p_NUMBER_VALUE3 => rmv_rec.INFORMATION221
570 ,p_NUMBER_VALUE4 => rmv_rec.INFORMATION222
571 ,p_DATE_VALUE1 => rmv_rec.INFORMATION166
572 ,p_DATE_VALUE2 => rmv_rec.INFORMATION167
573 ,p_DATE_VALUE3 => rmv_rec.INFORMATION306
574 ,p_DATE_VALUE4 => rmv_rec.INFORMATION307
575 ,p_BUSINESS_GROUP_ID => rmv_rec.INFORMATION4
576 ,p_object_version_number => l_ovn
577 );
578 get_elig_det_for_rmn (p_business_group_id => rmv_rec.INFORMATION4,
579 p_rmn_id => rmv_rec.INFORMATION161,
580 p_elig_prfl_id => l_elig_prfl_id,
581 p_criteria_short_code => l_criteria_short_code);
582
583 PQH_RBC_ELPRO.create_criteria
584 (p_criteria_code => l_criteria_short_code
585 ,p_char_value1 => rmv_rec.INFORMATION13
586 ,p_CHAR_VALUE2 => rmv_rec.INFORMATION14
587 ,p_CHAR_VALUE3 => rmv_rec.INFORMATION15
588 ,p_CHAR_VALUE4 => rmv_rec.INFORMATION16
589 ,p_NUMBER_VALUE1 => l_number_value1
590 ,p_NUMBER_VALUE2 => l_number_value2
591 ,p_NUMBER_VALUE3 => rmv_rec.INFORMATION221
592 ,p_NUMBER_VALUE4 => rmv_rec.INFORMATION222
593 ,p_DATE_VALUE1 => rmv_rec.INFORMATION166
594 ,p_DATE_VALUE2 => rmv_rec.INFORMATION167
595 ,p_DATE_VALUE3 => rmv_rec.INFORMATION306
596 ,p_DATE_VALUE4 => rmv_rec.INFORMATION307
597 ,p_BUSINESS_GROUP_ID => rmv_rec.INFORMATION4
598 ,p_effective_date => p_effective_date
599 ,p_elig_prfl_id => l_elig_prfl_id
600 );
601
602 hr_utility.set_location('rmv id'||l_rmv_id,20);
603 exception
604 when others then
605 hr_utility.set_location('issues in creating rmv'||rmv_rec.information5,30);
606 raise;
607 end;
608 hr_utility.set_location('rmv id is '||l_rmv_id,30);
609 elsif rmv_rec.dml_operation in ('UPDATE')
610 and l_ovn is not null
611 and l_rmv_id is not null then
612 hr_utility.set_location('Rate Node Value is being updated'||l_rmv_id,60);
613 -- hr_utility.set_location('plan ovn'||grd_rec.information222,60);
614
615 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'PQH_RT_MATRIX_NODE_VALUES',
616 p_key_column_name => 'NODE_VALUE_ID',
617 p_key_column_value => l_rmv_id);
618
619 hr_utility.set_location(' ovn is '||l_db_ovn,30);
620
621 if l_db_ovn <> l_ovn then
622
623 hr_utility.set_location('Invalid ovn no ', 20);
624 else
625 begin
626 PQH_RT_MATRIX_NODE_VALUES_API.update_rt_matrix_node_value
627 (p_effective_date => p_effective_date
628 ,p_NODE_VALUE_ID => l_rmv_id
629 ,p_RATE_MATRIX_NODE_ID => rmv_rec.INFORMATION161
630 ,p_SHORT_CODE => rmv_rec.INFORMATION12
631 ,p_CHAR_VALUE1 => rmv_rec.INFORMATION13
632 ,p_CHAR_VALUE2 => rmv_rec.INFORMATION14
633 ,p_CHAR_VALUE3 => rmv_rec.INFORMATION15
634 ,p_CHAR_VALUE4 => rmv_rec.INFORMATION16
635 ,p_NUMBER_VALUE1 => l_number_value2
636 ,p_NUMBER_VALUE2 => l_number_value2
637 ,p_NUMBER_VALUE3 => rmv_rec.INFORMATION221
638 ,p_NUMBER_VALUE4 => rmv_rec.INFORMATION222
639 ,p_DATE_VALUE1 => rmv_rec.INFORMATION166
640 ,p_DATE_VALUE2 => rmv_rec.INFORMATION167
641 ,p_DATE_VALUE3 => rmv_rec.INFORMATION306
642 ,p_DATE_VALUE4 => rmv_rec.INFORMATION307
643 ,p_BUSINESS_GROUP_ID => rmv_rec.INFORMATION4
644 ,p_object_version_number => l_ovn );
645 exception
646 when others then
647 hr_utility.set_location('issues in updating Node lalues'||l_rmv_id,70);
648 hr_utility.set_location('rmv ovn'||l_ovn,75);
649 hr_utility.set_location('rmv name'||substr(rmv_rec.information5,1,45),78);
650 raise;
651 end;
652 end if;
653 else
654 l_message_text := 'invalid operation '||rmv_rec.dml_operation
655 ||' rmv_id'||l_rmv_id
656 ||' rmv_ovn'||l_ovn
657 ||' rmv_name'||rmv_rec.information5;
658
659
660 end if;
661 end loop;
662 hr_utility.set_location('leaving '||l_proc,100);
663 exception
664 when others then
665 raise;
666
667 end stage_to_rmn_values;
668
669 procedure get_plan_det_for_rmn(p_rmn_id in number,
670 p_business_group_id in number,
671 p_effective_date in date,
672 p_pl_id out nocopy ben_pl_f.pl_id%type,
673 p_pl_name out nocopy ben_pl_f.name%type)
674 is
675 begin
676 hr_utility.set_location('Entering get_plan_name_for_rmn ',100);
677
678 select pl.pl_id pl_id, pl.name pl_name
679 into p_pl_id, p_pl_name
680 from ben_pl_f pl,
681 pqh_rate_matrix_nodes rmn
682 where rmn.pl_id = pl.pl_id
683 and p_effective_date between pl.effective_start_date and pl.effective_end_date
684 and rmn.rate_matrix_node_id = p_rmn_id
685 and rmn.business_group_id = p_business_group_id
686 and pl.business_group_id = p_business_group_id
687 and rmn.business_group_id = pl.business_group_id;
688
689
690 hr_utility.set_location('leaving get_plan_name_for_rmn',100);
691
692 exception
693 when others then
694 hr_utility.set_location('Issues in getting plan details',100);
695 raise;
696 End get_plan_det_for_rmn;
697
698 Function get_crit_rate_def_name (p_crd_id in number,
699 p_business_group_id in number)
700 return varchar2
701 is
702 l_crd_name varchar2(1000);
703 Begin
704 hr_utility.set_location('Entering get_crit_rate_def_name',100);
705
706 select name crd_name
707 into l_crd_name
708 from pqh_criteria_rate_defn_vl
709 where CRITERIA_RATE_DEFN_ID = p_crd_id
710 and business_group_id = p_business_group_id;
711
712 hr_utility.set_location('leaving get_crit_rate_def_name',100);
713
714 return l_crd_name;
715
716 End get_crit_rate_def_name;
717
718
719 function get_rmn_short_code(p_rmn_id in number,
720 p_business_group_id in number)
721 return varchar2 is
722 l_rmn_short_code varchar2(1000);
723 Begin
724 hr_utility.set_location('Entering get_rmn_short_code',100);
725
726 Select short_code
727 into l_rmn_short_code
728 from pqh_rate_matrix_nodes
729 where rate_matrix_node_id = p_rmn_id
730 and business_group_id = p_business_group_id;
731
732 hr_utility.set_location('leaving get_rmn_short_code',100);
733 return l_rmn_short_code;
734
735
736 End get_rmn_short_code;
737
738 Procedure stage_to_rmr(p_copy_entity_txn_id in number,
739 p_effective_date in date,
740 p_business_group_id in number,
741 p_datetrack_mode in varchar2,
742 p_rmn_id in number)
743 is
744
745 l_proc varchar2(61) :='stage_to_rmr';
746 l_rmr_id number;
747 l_seq number;
748 l_db_ovn number;
749 l_ovn number;
750 l_object varchar2(80);
751 l_effective_start_date date;
752 l_effective_end_date date;
753 l_message_text varchar2(2000);
754 l_dt_mode varchar2(1000);
755 l_effective_date date;
756 l_esd date;
757 l_esd_abr date;
758 l_eed_abr date;
759 l_abr_id number;
760 l_pl_name ben_pl_f.name%type;
761 l_pl_id ben_pl_f.pl_id%type;
762 l_crd_name pqh_criteria_rate_defn_vl.name%type;
763 l_ovn_abr number;
764 l_short_code_rmn varchar2(1000);
765 l_base_rt_exist varchar2(1);
766
767 cursor csr_rmr is
768 select *
769 from ben_copy_entity_results
770 where copy_entity_txn_id = p_copy_entity_txn_id
771 and table_alias = 'RMR'
772 and dml_operation in ('CREATE','UPDATE')
773 and information161 = p_rmn_id;
774
775 begin
776 hr_utility.set_location('inside'||l_proc,10);
777 for rmr_rec in csr_rmr loop
778 l_ovn := rmr_rec.Information265;
779 l_rmr_id := rmr_rec.Information1;
780 if rmr_rec.dml_operation = 'CREATE' then
781 hr_utility.set_location('new plan is being created'||l_proc,20);
782 begin
783 hr_utility.set_location('rmr id'||l_rmr_id,20);
784
785 select min(effective_start_date)
786 into l_esd
787 from ben_pl_f pl, pqh_rate_matrix_nodes rmn
788 where rmn.pl_id = pl.pl_id
789 and rmn.rate_matrix_node_id = p_rmn_id;
790
791 if l_esd < p_effective_date then
792 l_effective_date := l_esd;
793 else
794 l_effective_date := p_effective_date;
795 end if;
796
797 PQH_RATE_MATRIX_RATES_API.create_rate_matrix_rate
798 (p_effective_date => l_effective_date
799 ,p_rate_matrix_rate_id => l_rmr_id
800 ,p_EFFECTIVE_START_DATE => l_effective_start_date
801 ,p_EFFECTIVE_END_DATE => l_effective_end_date
802 ,p_RATE_MATRIX_NODE_ID => p_rmn_id
803 ,p_CRITERIA_RATE_DEFN_ID => rmr_rec.Information162
804 ,p_MIN_RATE_VALUE => nvl(rmr_rec.Information294,0)
805 ,p_MAX_RATE_VALUE => nvl(rmr_rec.Information295,0)
806 ,p_MID_RATE_VALUE => nvl(rmr_rec.Information296,0)
807 ,p_RATE_VALUE => nvl(rmr_rec.Information297,0)
808 ,p_BUSINESS_GROUP_ID => rmr_rec.Information4
809 ,p_object_version_number => l_ovn
810 );
811 hr_utility.set_location('rmr id'||l_rmr_id,20);
812
813 get_plan_det_for_rmn(p_rmn_id => p_rmn_id,
814 p_business_group_id => rmr_rec.Information4,
815 p_effective_date => l_effective_date,
816 p_pl_id => l_pl_id,
817 p_pl_name => l_pl_name);
818
819 hr_utility.set_location('Plan id and name are '||l_pl_id||' '||l_pl_name,20);
820
821 l_crd_name := get_crit_rate_def_name (p_crd_id => rmr_rec.Information162,
822 p_business_group_id => rmr_rec.Information4);
823
824
825 hr_utility.set_location('Criteria rate definition name is '||l_crd_name,20);
826
827 l_short_code_rmn := get_rmn_short_code(p_rmn_id => p_rmn_id,
828 p_business_group_id => rmr_rec.Information4);
829
830
831 hr_utility.set_location('Rate Matrix Node short code is '||l_short_code_rmn,20);
832
833 l_base_rt_exist := chk_acty_base_rate_exist(
834 p_pl_id => l_pl_id,
835 p_effective_date => l_effective_date,
836 p_business_group_id => p_business_group_id ,
837 P_MAPPING_TABLE_PK_ID => rmr_rec.Information162
838 );
839 if l_base_rt_exist = 'N' then
840 BEN_ACTY_BASE_RATE_API.CREATE_ACTY_BASE_RATE
841 (P_EFFECTIVE_DATE => l_effective_date
842 ,p_acty_base_rt_id => l_abr_id
843 ,p_effective_start_date => l_esd_abr
844 ,p_effective_end_date => l_eed_abr
845 ,P_BUSINESS_GROUP_ID => rmr_rec.Information4
846 ,P_ACTY_BASE_RT_STAT_CD => 'A'
847 ,P_ACTY_TYP_CD => 'RBC'
848 ,P_NAME => l_pl_name||'-'||l_short_code_rmn||'-'||l_crd_name
849 ,P_PL_ID => l_pl_id
850 ,P_RT_MLT_CD => 'NSVU'
851 ,P_ELE_RQD_FLAG => 'N'
852 ,P_MAPPING_TABLE_NAME => 'PQH_CRITERIA_RATE_DEFN'
853 ,P_MAPPING_TABLE_PK_ID => rmr_rec.Information162
854 ,p_object_version_number => l_ovn_abr
855 );
856 hr_utility.set_location('Base rate id and name is '||l_abr_id||' '||l_pl_name||' - '||l_crd_name,20);
857 else
858 hr_utility.set_location('Base rate already exist',20);
859 end if;
860
861 exception
862 when others then
863 hr_utility.set_location('issues in creating Rate_matrix node'||l_rmr_id,30);
864 raise;
865 end;
866 hr_utility.set_location('rmr id is '||l_rmr_id,30);
867 elsif rmr_rec.dml_operation in ('UPDATE')
868 and l_ovn is not null
869 and l_rmr_id is not null then
870 hr_utility.set_location('Rate Matrix node is being updated'||l_rmr_id,60);
871 hr_utility.set_location('Rate Matrix rate dt mode '||p_datetrack_mode,60);
872 if p_datetrack_mode <> 'CORRECTION' then
873 l_dt_mode := pqh_gsp_stage_to_ben.get_update_mode(p_table_name => 'PQH_RATE_MATRIX_RATES_F',
874 p_key_column_name => 'RATE_MATRIX_RATE_ID',
875 p_key_column_value => l_rmr_id,
876 p_effective_date => p_effective_date);
877 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
878 else
879 l_dt_mode := p_datetrack_mode;
880 end if;
881 -- hr_utility.set_location('plan ovn'||grd_rec.information222,60);
882 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'PQH_RATE_MATRIX_RATES_F',
883 p_key_column_name => 'RATE_MATRIX_RATE_ID',
884 p_key_column_value => l_rmr_id,
885 p_effective_date => p_effective_date);
886 hr_utility.set_location(' ovn is '||l_db_ovn,30);
887 if l_db_ovn <> l_ovn then
888 hr_utility.set_location('object verson not same ', 20);
889 else
890 begin
891 PQH_RATE_MATRIX_RATES_API.update_rate_matrix_rate
892 (p_effective_date => p_effective_date
893 ,p_datetrack_mode => l_dt_mode
894 ,p_RATE_MATRIX_RATE_ID => l_rmr_id
895 ,p_EFFECTIVE_START_DATE => l_effective_start_date
896 ,p_EFFECTIVE_END_DATE => l_effective_end_date
897 ,p_RATE_MATRIX_NODE_ID => rmr_rec.Information161
898 ,p_CRITERIA_RATE_DEFN_ID => rmr_rec.Information162
899 ,p_MIN_RATE_VALUE => rmr_rec.Information294
900 ,p_MAX_RATE_VALUE => rmr_rec.Information295
901 ,p_MID_RATE_VALUE => rmr_rec.Information296
902 ,p_RATE_VALUE => nvl(rmr_rec.Information297,0)
903 ,p_BUSINESS_GROUP_ID => rmr_rec.Information4
904 ,p_object_version_number => l_ovn);
905 exception
906 when others then
907 hr_utility.set_location('issues in updating rate matrix rate'||l_rmr_id,70);
908 hr_utility.set_location('rmr ovn'||l_ovn,75);
909 raise;
910 end;
911 end if;
912 else
913 l_message_text := 'invalid operation '||rmr_rec.dml_operation
914 ||' rmr_id'||l_rmr_id
915 ||' rmr_ovn'||l_ovn;
916 end if;
917 end loop;
918 hr_utility.set_location('leaving '||l_proc,100);
919 exception
920 when others then
921 raise;
922 End stage_to_rmr;
923
924
925
926 procedure rbc_data_push(p_copy_entity_txn_id in number,
927 p_effective_date in date,
928 p_business_group_id in number,
929 p_datetrack_mode in varchar2,
930 p_status out nocopy varchar2 ) is
931 l_datetrack_mode varchar2(30);
932 l_continue varchar2(30) := 'Y';
933 l_status varchar2(30) := 'YES';
934 begin
935
936 -- hr_utility.trace_on(NULL,'SJRBC');
937
938 hr_utility.set_location('inside rbc_data_push',10);
939 if p_datetrack_mode = 'OVERWRITE' then
940 l_datetrack_mode := 'CORRECTION';
941 elsif p_datetrack_mode = 'DATETRACK' then
942 l_datetrack_mode := 'UPDATE_OVERRIDE';
943 else
944 hr_utility.set_location('invalid dt mode passed'||p_datetrack_mode,10);
945 l_continue := 'N';
946 end if;
947 if l_continue = 'Y' then
948 begin
949
950 pre_push_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
951 p_effective_date => p_effective_date,
952 p_business_group_id => p_business_group_id,
953 P_Date_Track_Mode => l_datetrack_mode,
954 p_status => l_status );
955
956
957 if l_status = 'YES' then
958 hr_utility.set_location('pre push done ',20);
959 rbc_stage_to_hr(p_copy_entity_txn_id => p_copy_entity_txn_id,
960 p_effective_date => p_effective_date,
961 p_business_group_id => p_business_group_id,
962 p_datetrack_mode => l_datetrack_mode,
963 p_status => l_status);
964 end if;
965
966 if l_status = 'YES' then
967 hr_utility.set_location('data pushed to hr ',20);
968
969 begin
970
971 update pqh_copy_entity_txns
972 set status ='COMPLETED'
973 where copy_entity_txn_id = p_copy_entity_txn_id;
974
975 -- Purging the Copy Entity Txn record as it is no longer required --
976
977 Delete from Ben_Copy_Entity_Results
978 where Copy_Entity_Txn_Id = p_copy_entity_txn_id;
979 -- and table_alias in ('PLN','RMN','RMV','RMR','RBR',');
980
981
982 hr_utility.set_location('txn stat chg to comp',40);
983 exception
984 when others then
985 hr_utility.set_location('issues in updating cet row ',10);
986 l_status := 'NO';
987 raise;
988 end;
989
990 end if;
991
992 exception
993 when others then
994 hr_utility.set_location('issues in writing data ',10);
995 l_status := 'NO';
996 raise;
997 end;
998 end if;
999
1000 p_status := l_status ;
1001
1002
1003 hr_utility.set_location('Leaving: rbc_data_push', 10);
1004 -- hr_utility.trace_off;
1005 end rbc_data_push;
1006
1007 function get_pl_typ_id (p_effective_date in date,
1008 p_business_group_id in number)
1009 return number
1010 is
1011 l_pl_typ_id number;
1012 begin
1013
1014 hr_utility.set_location('Entering: get_pl_typ_id', 10);
1015
1016 select PL_TYP_ID
1017 into l_pl_typ_id
1018 from ben_pl_typ_f
1019 where p_effective_date between effective_start_date and effective_end_date
1020 and business_group_id = p_business_group_id
1021 and opt_typ_cd = 'RBC';
1022
1023 hr_utility.set_location('Leaving: get_pl_typ_id', 10);
1024
1025 return l_pl_typ_id;
1026 exception
1027 when too_many_rows then
1028 hr_utility.set_location('more than onr rows returned', 20);
1029 when others then
1030 hr_utility.set_location('Problem in getting pl_typ_id ', 20);
1031
1032 End get_pl_typ_id;
1033
1034
1035
1036 Procedure stage_to_plan(p_copy_entity_txn_id in number,
1037 p_business_group_id in number,
1038 p_effective_date in date,
1039 p_datetrack_mode in varchar2) is
1040 l_proc varchar2(61) :='stage_to_plan';
1041 l_plan_id number;
1042 l_seq number;
1043 l_db_ovn number;
1044 l_ovn number;
1045 l_object varchar2(80);
1046 l_effective_start_date date;
1047 l_effective_end_date date;
1048 l_message_text varchar2(2000);
1049 l_pl_typ_id number;
1050 l_dt_mode varchar2(1000);
1051 cursor csr_plan is
1052 select *
1053 from ben_copy_entity_results
1054 where copy_entity_txn_id = p_copy_entity_txn_id
1055 and table_alias = 'PLN'
1056 and dml_operation in ('CREATE','UPDATE');
1057 begin
1058 hr_utility.set_location('inside'||l_proc,10);
1059 for plan_rec in csr_plan loop
1060 l_ovn := plan_rec.Information265;
1061 l_plan_id := plan_rec.Information1;
1062 if plan_rec.dml_operation = 'CREATE' then
1063 hr_utility.set_location('new plan is being created'||l_proc,20);
1064 begin
1065 hr_utility.set_location('plan name'||substr(plan_rec.information170,1,45),20);
1066 hr_utility.set_location('plan id'||l_plan_id,20);
1067 l_pl_typ_id := get_pl_typ_id(p_effective_date,plan_rec.Information4);
1068 hr_utility.set_location('plan typ id'||l_pl_typ_id,20);
1069 ben_plan_api.create_Plan(
1070 p_pl_id => l_plan_id
1071 ,p_effective_start_date => l_effective_start_date
1072 ,p_effective_end_date => l_effective_end_date
1073 ,p_name => plan_rec.Information170
1074 ,p_pl_stat_cd => plan_rec.Information19
1075 ,p_object_version_number => l_ovn
1076 ,p_effective_date => p_effective_date
1077 ,p_short_name => plan_rec.Information94
1078 ,p_short_code => plan_rec.Information93
1079 ,p_business_group_id => plan_rec.Information4
1080 ,p_pl_cd => 'MYNTBPGM'
1081 ,p_pl_typ_id => l_pl_typ_id
1082 ,p_vrfy_fmly_mmbr_cd => null
1083 );
1084 hr_utility.set_location('paln id'||l_plan_id,20);
1085
1086 exception
1087 when others then
1088 hr_utility.set_location('issues in creating plan'||plan_rec.information170,30);
1089 raise;
1090 end;
1091 hr_utility.set_location('plan id is '||l_plan_id,30);
1092 elsif plan_rec.dml_operation in ('UPDATE')
1093 and l_ovn is not null
1094 and l_plan_id is not null then
1095 hr_utility.set_location('Plan is being updated'||l_plan_id,60);
1096 -- hr_utility.set_location('plan ovn'||grd_rec.information222,60);
1097 if p_datetrack_mode <> 'CORRECTION' then
1098 l_dt_mode := pqh_gsp_stage_to_ben.get_update_mode(p_table_name => 'BEN_PL_F',
1099 p_key_column_name => 'PL_ID',
1100 p_key_column_value => l_plan_id,
1101 p_effective_date => p_effective_date);
1102 hr_utility.set_location(' dt mode is '||l_dt_mode,30);
1103 else
1104 l_dt_mode := p_datetrack_mode;
1105 end if;
1106 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'BEN_PL_F',
1107 p_key_column_name => 'PL_ID',
1108 p_key_column_value => l_plan_id,
1109 p_effective_date => p_effective_date);
1110 hr_utility.set_location(' ovn is '||l_db_ovn,30);
1111 if l_db_ovn <> l_ovn then
1112 l_object := hr_general.decode_lookup('PQH_GSP_OBJECT_TYPE','PLN');
1113 fnd_message.set_name('PQH','PQH_GSP_OBJ_OVN_INVALID');
1114 fnd_message.set_token('OBJECT ',l_object);
1115 fnd_message.set_token('OBJECT_NAME ',plan_rec.information170);
1116 fnd_message.raise_error;
1117 else
1118 begin
1119 BEN_PLAN_API.update_Plan(
1120 p_pl_id => l_plan_id
1121 ,p_effective_start_date => l_effective_start_date
1122 ,p_effective_end_date => l_effective_end_date
1123 ,p_name => plan_rec.information170
1124 ,p_pl_stat_cd => plan_rec.Information19
1125 ,p_business_group_id => plan_rec.Information4
1126 ,p_object_version_number => l_ovn
1127 ,p_effective_date => p_effective_date
1128 ,p_datetrack_mode => l_dt_mode
1129 ,p_short_name => plan_rec.Information94
1130 ,p_short_code => plan_rec.Information93
1131 ,p_vrfy_fmly_mmbr_cd => null);
1132 exception
1133 when others then
1134 hr_utility.set_location('issues in updating plan'||l_plan_id,70);
1135 hr_utility.set_location('paln ovn'||l_ovn,75);
1136 hr_utility.set_location('plan name'||substr(plan_rec.information170,1,45),78);
1137 raise;
1138 end;
1139 end if;
1140 else
1141 l_message_text := 'invalid operation '||plan_rec.dml_operation
1142 ||' plan_id'||l_plan_id
1143 ||' plan_ovn'||l_ovn
1144 ||' plan_name'||plan_rec.information170;
1145
1146 end if;
1147
1148 -- writeback(plan_rec.copy_entity_result_id,l_plan_id,p_copy_entity_txn_id);
1149 g_pln_short_code := plan_rec.Information93;
1150
1151 stage_to_rmn(p_copy_entity_txn_id => p_copy_entity_txn_id,
1152 p_effective_date => p_effective_date,
1153 p_business_group_id => p_business_group_id,
1154 p_plan_id => l_plan_id,
1155 p_datetrack_mode => p_datetrack_mode);
1156
1157 g_pln_short_code := null;
1158 hr_utility.set_location('Rate_matrix_node row updated',40);
1159
1160 end loop;
1161
1162 hr_utility.set_location('leaving '||l_proc,100);
1163
1164 exception
1165 when others then
1166 raise;
1167 end stage_to_plan;
1168
1169 Procedure stage_to_rmn(p_copy_entity_txn_id in number,
1170 p_business_group_id in number,
1171 p_effective_date in date,
1172 p_plan_id in number,
1173 p_datetrack_mode in varchar2
1174 ) is
1175 l_proc varchar2(61) :='stage_to_rmn';
1176 l_rmn_id number;
1177 l_seq number;
1178 l_db_ovn number;
1179 l_ovn number;
1180 l_object varchar2(80);
1181 l_message_text varchar2(2000);
1182 l_short_code varchar2(1000);
1183 l_parent_rmn_id number;
1184 l_elp_id number;
1185 cursor csr_rmn is
1186 select *
1187 from ben_copy_entity_results
1188 where copy_entity_txn_id = p_copy_entity_txn_id
1189 and table_alias = 'RMN'
1190 and dml_operation in ('CREATE','UPDATE')
1191 order by information160;
1192 begin
1193 hr_utility.set_location('inside'||l_proc,10);
1194 for rmn_rec in csr_rmn loop
1195 l_ovn := rmn_rec.Information265;
1196 l_rmn_id := rmn_rec.Information1;
1197
1198 l_parent_rmn_id := get_parent_rmn(p_copy_entity_txn_id => p_copy_entity_txn_id,
1199 p_copy_entity_result_id => rmn_rec.GS_PARENT_ENTITY_RESULT_ID);
1200
1201 if rmn_rec.dml_operation = 'CREATE' then
1202 hr_utility.set_location('new Rate matrix node is being created'||l_proc,20);
1203 begin
1204 hr_utility.set_location('Rate Matrix Node name'||substr(rmn_rec.information219,1,45),20);
1205 l_short_code := rmn_rec.INFORMATION12;
1206 hr_utility.set_location('Short_code is '||l_short_code,20);
1207 if l_short_code is null then
1208 hr_utility.set_location('Short_code is null',20);
1209 l_short_code := get_short_code('RMN');
1210 hr_utility.set_location('Short_code generated'||l_short_code,20);
1211 end if;
1212 hr_utility.set_location('RMN id'||l_rmn_id,20);
1213
1214 -- Creating Eligibilty profile for each node
1215 if rmn_rec.Information160 > 1 then
1216 pqh_rbc_elpro.create_elpro(p_name => l_short_code,
1217 p_description => rmn_rec.Information219,
1218 p_business_group_id => rmn_rec.Information4,
1219 p_effective_date => p_effective_date,
1220 p_elig_prfl_id => l_elp_id);
1221 else
1222 l_elp_id := null;
1223 end if;
1224
1225 PQH_RATE_MATRIX_NODES_API.create_rate_matrix_node
1226 (p_effective_date => p_effective_date
1227 ,p_rate_matrix_node_id => l_rmn_id
1228 ,p_short_code => l_short_code
1229 ,p_pl_id => p_plan_id
1230 ,p_level_number => rmn_rec.Information160
1231 ,p_criteria_short_code => rmn_rec.INFORMATION13
1232 ,p_node_name => rmn_rec.Information219
1233 ,p_parent_node_id => l_parent_rmn_id
1234 ,p_eligy_prfl_id => l_elp_id
1235 ,p_business_group_id => rmn_rec.Information4
1236 ,p_object_version_number => l_ovn
1237 );
1238 hr_utility.set_location('rmn id'||l_rmn_id,20);
1239 exception
1240 when others then
1241 hr_utility.set_location('issues in creating rmn'||rmn_rec.information219,30);
1242 raise;
1243 end;
1244 hr_utility.set_location('rmn id is '||l_rmn_id,30);
1245 elsif rmn_rec.dml_operation in ('UPDATE')
1246 and l_ovn is not null
1247 and l_rmn_id is not null then
1248 hr_utility.set_location('Rate Matrix Node is being updated'||l_rmn_id,60);
1249 l_db_ovn := pqh_gsp_stage_to_ben.get_ovn(p_table_name => 'PQH_RATE_MATRIX_NODES',
1250 p_key_column_name => 'RATE_MATRIX_NODE_ID',
1251 p_key_column_value => l_rmn_id
1252 );
1253 hr_utility.set_location(' ovn is '||l_db_ovn,30);
1254 if l_db_ovn <> l_ovn then
1255 fnd_message.set_name('PQH','PQH_RBC_OBJ_OVN_INVALID');
1256 fnd_message.raise_error;
1257 else
1258 begin
1259 PQH_RATE_MATRIX_NODES_API.update_rate_matrix_node
1260 (p_effective_date => p_effective_date
1261 ,p_rate_matrix_node_id => l_rmn_id
1262 ,p_short_code => rmn_rec.INFORMATION12
1263 ,p_pl_id => p_plan_id
1264 ,p_level_number => rmn_rec.Information160
1265 ,p_criteria_short_code => rmn_rec.INFORMATION13
1266 ,p_node_name => rmn_rec.Information219
1267 ,p_parent_node_id => l_parent_rmn_id
1268 ,p_eligy_prfl_id => rmn_rec.Information169
1269 ,p_business_group_id => rmn_rec.Information4
1270 ,p_object_version_number => l_ovn
1271 );
1272 exception
1273 when others then
1274 hr_utility.set_location('issues in updating Rate Matrix node'||l_rmn_id,70);
1275 hr_utility.set_location('rmn ovn'||l_ovn,75);
1276 hr_utility.set_location('rate Matix Node name'||substr(rmn_rec.information170,1,45),78);
1277 raise;
1278 end;
1279 end if;
1280 else
1281 l_message_text := 'invalid operation '||rmn_rec.dml_operation
1282 ||' rmn_id'||l_rmn_id
1283 ||' rmn_ovn'||l_ovn
1284 ||' rmn_name'||rmn_rec.information170;
1285
1286 end if;
1287
1288 hr_utility.set_location('IN RMN COPY ENTITY RESULT ID '||to_char(rmn_rec.copy_entity_result_id),201);
1289 hr_utility.set_location('IN RMN rmn id is'||to_char(l_rmn_id),202);
1290 hr_utility.set_location('IN RMN copy entity txn id '||p_copy_entity_txn_id,203);
1291
1292 rmn_writeback(p_copy_entity_result_id => rmn_rec.copy_entity_result_id,
1293 p_rmn_id => l_rmn_id,
1294 p_copy_entity_txn_id => p_copy_entity_txn_id);
1295
1296 stage_to_rmn_values(p_copy_entity_txn_id => p_copy_entity_txn_id,
1297 p_business_group_id => p_business_group_id,
1298 p_effective_date => p_effective_date,
1299 p_rmn_id => l_rmn_id );
1300
1301 stage_to_rmr(p_copy_entity_txn_id => p_copy_entity_txn_id,
1302 p_effective_date => p_effective_date,
1303 p_business_group_id => p_business_group_id,
1304 p_datetrack_mode => p_datetrack_mode,
1305 p_rmn_id => l_rmn_id );
1306
1307 hr_utility.set_location('Rate_matrix_rates row updated',40);
1308 hr_utility.set_location('Rate_matrix_node_values created if any reqd',41);
1309
1310 end loop;
1311 hr_utility.set_location('leaving '||l_proc,100);
1312 exception
1313 when others then
1314 raise;
1315 end stage_to_rmn;
1316
1317 procedure rbc_stage_to_hr(p_copy_entity_txn_id in number,
1318 p_effective_date in date,
1319 p_business_group_id in number,
1320 p_datetrack_mode in varchar2,
1321 p_status out nocopy varchar2
1322 ) is
1323
1324 -- this procedure will be the callable routine
1325 -- in this procedure we will traverse the hierarchy and find out what all is
1326 -- hr data
1327 -- for any plan created/updated stage_to_plan
1328 -- for any option created/ updated stage_to_Rate_matrix_node
1329 -- for any oipl created/ updated stage_to_Rate_matrix_node_value
1330 -- for any standard rate created/ updated stage_to_Rate_matrix_rates
1331 /* the data should be written in this order
1332 1) Plan
1333 2) Rate_matrix_node
1334 3) Rate_matrix_node_values
1335 4) Rate_matrix_Rates
1336 */
1337 l_proc varchar2(61) := 'rbc_stage_to_hr' ;
1338 l_effective_date date := p_effective_date;
1339 l_return varchar2(3) := 'YES';
1340 begin
1341 hr_utility.set_location('inside '||l_proc,10);
1342 hr_utility.set_location('cet is '||p_copy_entity_txn_id,1);
1343 hr_utility.set_location('bg is '||p_business_group_id,2);
1344 hr_utility.set_location('dt mode is '||p_datetrack_mode,4);
1345
1346 stage_to_plan(p_copy_entity_txn_id => p_copy_entity_txn_id,
1347 p_effective_date => l_effective_date,
1348 p_business_group_id => p_business_group_id,
1349 p_datetrack_mode => p_datetrack_mode);
1350
1351 hr_utility.set_location('plan row checked for update',30);
1352
1353
1354
1355 p_status := l_return ;
1356 hr_utility.set_location('Leaving '||l_proc,10);
1357 exception
1358 when others then
1359 hr_utility.set_location('error encountered',420);
1360 p_status := 'NO';
1361 raise;
1362 end rbc_stage_to_hr;
1363
1364 end pqh_rbc_stage_to_rbc;