[Home] [Help]
PACKAGE BODY: APPS.PQH_RBC_LOAD_OBJECTS
Source
1 package body pqh_rbc_load_objects as
2 /* $Header: pqhrbcld.pkb 120.10.12000000.2 2007/04/19 12:44:33 brsinha noship $ */
3
4 function get_plan_id(p_short_code in varchar2,
5 p_effective_date in date,
6 p_business_group_id in number)
7 return varchar2 is
8 Cursor csr_pl is
9 select pl_id from ben_pl_f
10 where short_code = p_short_code
11 and p_effective_date between effective_start_date and effective_end_date
12 and business_group_id = p_business_group_id;
13 l_pl_id ben_pl_f.pl_id%type ;
14
15 begin
16 Open csr_pl;
17 Fetch csr_pl into l_pl_id;
18 Close csr_pl;
19
20 return l_pl_id;
21 end;
22
23
24 --
25 --
26 -- The following procedure will be called when loading a rate matrix node.
27 --
28 Procedure load_rate_matrix_row
29 (p_pl_short_code in varchar2
30 ,p_name in varchar2
31 ,p_short_name in varchar2
32 ,p_pl_stat_cd in varchar2
33 ,p_pl_cd in varchar2
34 ,p_legislation_code in varchar2
35 ,p_effective_start_date in varchar2
36 ,p_owner in varchar2
37 ) is
38
39 --
40 l_pl_id ben_pl_f.pl_id%type ;
41 l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
42 l_ovn number ;
43 l_effective_start_date date ;
44 l_effective_end_date date ;
45 l_effective_date date;
46 l_business_group_id hr_all_organization_units.business_group_id%type;
47 l_dt_mode varchar2(30);
48 --
49 --
50 l_created_by ben_pl_f.created_by%TYPE;
51 l_last_updated_by ben_pl_f.last_updated_by%TYPE;
52 l_creation_date ben_pl_f.creation_date%TYPE;
53 l_last_update_date ben_pl_f.last_update_date%TYPE;
54 l_last_update_login ben_pl_f.last_update_login%TYPE;
55 --
56 -- Create a plan corresponding to Rate matrix.
57 --
58 Cursor csr_pl is
59 select pl_id,object_version_number from ben_pl_f
60 where short_code = p_pl_short_code
61 and l_effective_date between effective_start_date and effective_end_date
62 and business_group_id = l_business_group_id;
63 --
64 -- There can be only one plan type in the business group with option type = 'RBC'
65 --
66 Cursor csr_pl_typ is
67 select pl_typ_id
68 From ben_pl_typ_f
69 Where opt_typ_cd = 'RBC'
70 and l_effective_date between effective_start_date and effective_end_date
71 and business_group_id = l_business_group_id;
72 --
73 Begin
74 --
75 -- populate WHO columns
76 --
77 /**
78 if p_owner = 'SEED' then
79 l_created_by := 1;
80 l_last_updated_by := 1;
81 else
82 l_created_by := 0;
83 l_last_updated_by := 0;
84 end if;
85 **/
86 l_last_updated_by := fnd_load_util.owner_id(p_owner);
87 l_created_by := fnd_load_util.owner_id(p_owner);
88
89
90 l_creation_date := sysdate;
91 l_last_update_date := sysdate;
92 l_last_update_login := 0;
93 l_effective_date := nvl(to_date(p_effective_start_date,'DD/MM/YYYY'),sysdate);
94 --
95 l_business_group_id := fnd_global.PER_BUSINESS_GROUP_ID ;
96 --
97 Open csr_pl;
98 Fetch csr_pl into l_pl_id,l_ovn;
99 Close csr_pl;
100
101 -- No plan exists corresponding to Rate matrix short code
102 --
103 If l_pl_id is null then
104
105 Open csr_pl_typ;
106 Fetch csr_pl_typ into l_pl_typ_id;
107 Close csr_pl_typ;
108 --
109 If l_pl_typ_id is null then
110
111 ben_PLAN_TYPE_api.create_PLAN_TYPE
112 (
113 p_validate => false
114 ,p_pl_typ_id => l_pl_typ_id
115 ,p_effective_start_date => l_effective_start_date
116 ,p_effective_end_date => l_effective_end_date
117 ,p_no_mx_enrl_num_dfnd_flag => 'N'
118 ,p_no_mn_enrl_num_dfnd_flag => 'N'
119 ,p_name => 'Rate By Criteria'
120 ,p_pl_typ_stat_cd => 'A'
121 ,p_opt_typ_cd => 'RBC'
122 ,p_business_group_id => l_business_group_id
123 ,p_object_version_number => l_ovn
124 ,p_effective_date => l_effective_date
125 ,p_short_name => 'RBC'
126 ,p_short_code => 'RBC'
127 );
128 End if;
129
130 BEN_PLAN_API.CREATE_PLAN(
131 P_EFFECTIVE_DATE => l_effective_date
132 ,P_BUSINESS_GROUP_ID => l_business_group_id
133 ,P_NAME => p_name
134 ,P_PL_CD => 'MYNTBPGM'
135 ,P_PL_ID => l_pl_id
136 ,P_PL_STAT_CD => p_pl_stat_cd
137 ,P_PL_TYP_ID => l_pl_typ_id
138 ,P_SHORT_CODE => p_pl_short_code
139 ,P_SHORT_NAME => p_short_name
140 ,P_EFFECTIVE_START_DATE => l_effective_start_date
141 ,P_EFFECTIVE_END_DATE => l_effective_end_date
142 ,P_OBJECT_VERSION_NUMBER => l_ovn
143 );
144 Else
145 l_dt_mode := pqh_gsp_stage_to_ben.get_update_mode('BEN_PL_F','PL_ID', l_pl_id, l_effective_date) ;
146
147
148 BEN_PLAN_API.UPDATE_PLAN(
149 P_EFFECTIVE_DATE => l_effective_date
150 ,P_BUSINESS_GROUP_ID => l_business_group_id
151 ,P_NAME => p_name
152 ,P_PL_ID => l_pl_id
153 ,P_PL_STAT_CD => p_pl_stat_cd
154 ,P_PL_CD => p_pl_cd
155 ,P_RT_STRT_DT_RL => ''
156 ,P_VRFY_FMLY_MMBR_RL => ''
157 ,P_SHORT_CODE => p_pl_short_code
158 ,P_SHORT_NAME => p_short_name
159 ,P_EFFECTIVE_START_DATE => l_effective_start_date
160 ,P_EFFECTIVE_END_DATE => l_effective_end_date
161 ,P_OBJECT_VERSION_NUMBER => l_ovn
162 ,P_DATETRACK_MODE => l_dt_mode
163 );
164 End if;
165 --
166 End;
167 --
168 Procedure load_rmn_row
169 (p_pl_short_code in varchar2
170 ,p_node_short_code in varchar2
171 ,p_node_name in varchar2
172 ,p_level_number in varchar2
173 ,p_criteria_short_code in varchar2
174 ,p_parent_node_short_code in varchar2
175 ,p_eligy_prfl_name in varchar2
176 ,p_legislation_code in varchar2
177 ,p_effective_date in varchar2
178 ,p_owner in varchar2
179 ) is
180 --
181 l_pl_id ben_pl_f.pl_id%type ;
182 l_eligy_prfl_id ben_eligy_prfl_f.eligy_prfl_id%type;
183 l_business_group_id hr_all_organization_units.business_group_id%type;
184 --
185 l_rate_matrix_node_id pqh_rate_matrix_nodes.rate_matrix_node_id%type;
186 l_parent_node_id pqh_rate_matrix_nodes.parent_node_id%type;
187 l_ovn number := 1;
188 l_effective_date date;
189 --
190 --
191 l_created_by pqh_rate_matrix_nodes.created_by%TYPE;
192 l_last_updated_by pqh_rate_matrix_nodes.last_updated_by%TYPE;
193 l_creation_date pqh_rate_matrix_nodes.creation_date%TYPE;
194 l_last_update_date pqh_rate_matrix_nodes.last_update_date%TYPE;
195 l_last_update_login pqh_rate_matrix_nodes.last_update_login%TYPE;
196 --
197 --
198 --
199 -- rate matrix node
200 --
201 Cursor csr_rmn(p_code in varchar2) is
202 select rate_matrix_node_id
203 from pqh_rate_matrix_nodes
204 Where short_code = p_code
205 and business_group_id = l_business_group_id;
206 --
207 -- plan corresponding to Rate matrix.
208 --
209 /* Cursor csr_pl is
210 select pl_id from ben_pl_f
211 where short_code = p_pl_short_code
212 and l_effective_date between effective_start_date and effective_end_date
213 and business_group_id = l_business_group_id;
214 */
215
216 -- replaced with pl_id := getPlanId(p_pl_short_code,l_effective_date,l_business_group_id);
217 --
218 -- Find eligibility profile
219 --
220 Cursor csr_elg is
221 select eligy_prfl_id from ben_eligy_prfl_f
222 where name = p_eligy_prfl_name
223 and l_effective_date between effective_start_date and effective_end_date
224 and business_group_id = l_business_group_id;
225 --
226 --
227 Begin
228 --
229 -- populate WHO columns
230 --
231 /**
232 if p_owner = 'SEED' then
233 l_created_by := 1;
234 l_last_updated_by := 1;
235 else
236 l_created_by := 0;
237 l_last_updated_by := 0;
238 end if;
239 **/
240 l_last_updated_by := fnd_load_util.owner_id(p_owner);
241 l_created_by := fnd_load_util.owner_id(p_owner);
242
243
244 l_creation_date := sysdate;
245 l_last_update_date := sysdate;
246 l_last_update_login := 0;
247 l_effective_date := nvl(to_date(p_effective_date,'DD/MM/YYYY'),sysdate);
248 --
249 l_business_group_id := fnd_global.PER_BUSINESS_GROUP_ID ;
250 --
251 /*
252 Open csr_pl;
253 Fetch csr_pl into l_pl_id;
254 Close csr_pl;
255 */
256
257 l_pl_id := get_plan_id(p_pl_short_code,l_effective_date,l_business_group_id);
258 --
259 Open csr_elg;
260 Fetch csr_elg into l_eligy_prfl_id;
261 Close csr_elg;
262 --
263 Open csr_rmn(p_node_short_code);
264 Fetch csr_rmn into l_rate_matrix_node_id;
265 Close csr_rmn;
266
267
268 Open csr_rmn(p_parent_node_short_code);
269 Fetch csr_rmn into l_parent_node_id;
270 Close csr_rmn;
271
272 -- No rate matrix node exists corresponding to the node_short_code
273 --
274 If l_rate_matrix_node_id is not null then
275
276 --
277 Update pqh_rate_matrix_nodes
278 set
279 RATE_MATRIX_NODE_ID = l_rate_matrix_node_id,
280 SHORT_CODE = p_node_short_code,
281 PL_ID = l_pl_id,
282 LEVEL_NUMBER = p_level_number,
283 CRITERIA_SHORT_CODE = p_criteria_short_code,
284 NODE_NAME = p_node_name,
285 PARENT_NODE_ID = l_parent_node_id,
286 ELIGY_PRFL_ID = l_eligy_prfl_id,
287 BUSINESS_GROUP_ID = l_business_group_id,
288 LEGISLATION_CODE = p_legislation_code,
289 last_updated_by = l_last_updated_by,
290 last_update_date = l_last_update_date,
291 last_update_login = l_last_update_login
292 Where rate_matrix_node_id = l_rate_matrix_node_id;
293 --
294 Else
295 --
296 Insert into pqh_rate_matrix_nodes
297 (
298 RATE_MATRIX_NODE_ID,
299 SHORT_CODE,
300 PL_ID,
301 LEVEL_NUMBER,
302 CRITERIA_SHORT_CODE,
303 NODE_NAME,
304 PARENT_NODE_ID,
305 ELIGY_PRFL_ID,
306 BUSINESS_GROUP_ID,
307 LEGISLATION_CODE,
308 CREATED_BY,
309 CREATION_DATE,
310 LAST_UPDATED_BY,
311 LAST_UPDATE_DATE,
312 LAST_UPDATE_LOGIN,
313 OBJECT_VERSION_NUMBER
314 )
315 Values
316 (
317 pqh_rate_matrix_nodes_s.nextval,
318 p_node_short_code,
319 l_pl_id,
320 p_level_number,
321 p_criteria_short_code,
322 p_node_name,
323 l_parent_node_id,
324 l_eligy_prfl_id,
325 l_business_group_id,
326 p_legislation_code,
327 l_created_by,
328 l_creation_date,
329 l_last_updated_by,
330 l_last_update_date,
331 l_last_update_login,
332 l_ovn
333 );
334
335 End if;
336 --
337 End load_rmn_row;
338 --
339 --
340 Procedure load_rmv_row
341 (p_short_code in varchar2
342 ,p_node_short_code in varchar2
343 ,p_char_value1 in varchar2
344 ,p_char_value2 in varchar2
345 ,p_char_value3 in varchar2
346 ,p_char_value4 in varchar2
347 ,p_number_value1 in varchar2
348 ,p_number_value2 in varchar2
349 ,p_number_value3 in varchar2
350 ,p_number_value4 in varchar2
351 ,p_date_value1 in varchar2
352 ,p_date_value2 in varchar2
353 ,p_date_value3 in varchar2
354 ,p_date_value4 in varchar2
355 ,p_legislation_code in varchar2
356 ,p_effective_date in varchar2
357 ,p_owner in varchar2
358 ) is
359 --
360 l_business_group_id hr_all_organization_units.business_group_id%type;
361 l_rate_matrix_node_id pqh_rate_matrix_nodes.rate_matrix_node_id%type;
362 --
363 l_node_value_id pqh_rt_matrix_node_values.node_value_id%type;
364 l_ovn number := 1;
365 l_effective_date date;
366 --
367 --
368 l_created_by pqh_rt_matrix_node_values.created_by%TYPE;
369 l_last_updated_by pqh_rt_matrix_node_values.last_updated_by%TYPE;
370 l_creation_date pqh_rt_matrix_node_values.creation_date%TYPE;
371 l_last_update_date pqh_rt_matrix_node_values.last_update_date%TYPE;
372 l_last_update_login pqh_rt_matrix_node_values.last_update_login%TYPE;
373 --
374 --
375 --
376 -- rate matrix node
377 --
378 Cursor csr_rmn(p_code in varchar2) is
379 select rate_matrix_node_id
380 from pqh_rate_matrix_nodes
381 Where short_code = p_code
382 and business_group_id = l_business_group_id;
383 --
384 --
385 Cursor csr_rmv is
386 select node_value_id
387 from pqh_rt_matrix_node_values
388 Where short_code = p_short_code
389 and business_group_id = l_business_group_id;
390 --
391 Begin
392 --
393 -- populate WHO columns
394 --
395 /**
396 if p_owner = 'SEED' then
397 l_created_by := 1;
398 l_last_updated_by := 1;
399 else
400 l_created_by := 0;
401 l_last_updated_by := 0;
402 end if;
403 **/
404 l_last_updated_by := fnd_load_util.owner_id(p_owner);
405 l_created_by := fnd_load_util.owner_id(p_owner);
406
407
408 l_creation_date := sysdate;
409 l_last_update_date := sysdate;
410 l_last_update_login := 0;
411 l_effective_date := nvl(to_date(p_effective_date,'DD/MM/YYYY'),sysdate);
412 --
413 l_business_group_id := fnd_global.PER_BUSINESS_GROUP_ID ;
414 --
415 --
416 Open csr_rmn(p_node_short_code);
417 Fetch csr_rmn into l_rate_matrix_node_id;
418 Close csr_rmn;
419 --
420 Open csr_rmv;
421 Fetch csr_rmv into l_node_value_id;
422 Close csr_rmv;
423
424 -- rate matrix node value exists corresponding to the short_code
425 --
426 If l_node_value_id is not null then
427
428 --
429 Update pqh_rt_matrix_node_values
430 set
431 RATE_MATRIX_NODE_ID = l_rate_matrix_node_id,
432 SHORT_CODE = p_short_code,
433 CHAR_VALUE1 = p_char_value1,
434 CHAR_VALUE2 = p_char_value2,
435 CHAR_VALUE3 = p_char_value3,
436 CHAR_VALUE4 = p_char_value4,
437 NUMBER_VALUE1 = p_number_value1,
438 NUMBER_VALUE2 = p_number_value2,
439 NUMBER_VALUE3 = p_number_value3,
440 NUMBER_VALUE4 = p_number_value4,
441 DATE_VALUE1 = to_date(p_date_value1,'DD/MM/YYYY'),
442 DATE_VALUE2 = to_date(p_date_value2,'DD/MM/YYYY'),
443 DATE_VALUE3 = to_date(p_date_value3,'DD/MM/YYYY'),
444 DATE_VALUE4 = to_date(p_date_value4,'DD/MM/YYYY'),
445 BUSINESS_GROUP_ID = l_business_group_id,
446 LEGISLATION_CODE = p_legislation_code,
447 last_updated_by = l_last_updated_by,
448 last_update_date = l_last_update_date,
449 last_update_login = l_last_update_login
450 Where node_value_id = l_node_value_id;
451 --
452 Else
453 --
454 Insert into pqh_rt_matrix_node_values
455 (
456 NODE_VALUE_ID ,
457 RATE_MATRIX_NODE_ID ,
458 SHORT_CODE ,
459 CHAR_VALUE1 ,
460 CHAR_VALUE2 ,
461 CHAR_VALUE3 ,
462 CHAR_VALUE4 ,
463 NUMBER_VALUE1 ,
464 NUMBER_VALUE2 ,
465 NUMBER_VALUE3 ,
466 NUMBER_VALUE4 ,
467 DATE_VALUE1 ,
468 DATE_VALUE2 ,
469 DATE_VALUE3 ,
470 DATE_VALUE4 ,
471 BUSINESS_GROUP_ID ,
472 LEGISLATION_CODE ,
473 CREATED_BY ,
474 CREATION_DATE ,
475 LAST_UPDATED_BY ,
476 LAST_UPDATE_DATE ,
477 LAST_UPDATE_LOGIN ,
478 OBJECT_VERSION_NUMBER
479 )
480 Values
481 (
482 pqh_rt_matrix_node_values_s.nextval,
483 l_rate_matrix_node_id,
484 p_short_code,
485 p_char_value1,
486 p_char_value2,
487 p_char_value3,
488 p_char_value4,
489 p_number_value1,
490 p_number_value2,
491 p_number_value3,
492 p_number_value4,
493 to_date(p_date_value1,'DD/MM/YYYY'),
494 to_date(p_date_value2,'DD/MM/YYYY'),
495 to_date(p_date_value3,'DD/MM/YYYY'),
496 to_date(p_date_value4,'DD/MM/YYYY'),
497 l_business_group_id,
498 p_legislation_code,
499 l_created_by,
500 l_creation_date,
501 l_last_updated_by,
502 l_last_update_date,
503 l_last_update_login,
504 l_ovn
505 );
506
507 End if;
508 --
509 End;
510 --
511 Procedure load_rmr_row
512 (p_node_short_code in varchar2
513 ,p_crit_rt_defn_short_code in varchar2
514 ,p_min_rate_value in varchar2
515 ,p_max_rate_value in varchar2
516 ,p_mid_rate_value in varchar2
517 ,p_rate_value in varchar2
518 ,p_legislation_code in varchar2
519 ,p_effective_start_date in varchar2
520 ,p_owner in varchar2
521 ) is
522
523 --
524 l_crd_id pqh_criteria_rate_defn.criteria_rate_defn_id%type;
525 l_crd_name pqh_criteria_rate_defn.short_name%type;
526 l_rate_matrix_node_id pqh_rate_matrix_rates_f.rate_matrix_node_id%type;
527 l_rt_matrix_rate_id pqh_rate_matrix_rates_f.rate_matrix_rate_id%type;
528 l_pl_id ben_pl_f.pl_id%type;
529 l_pl_name ben_pl_f.short_code%type;
530 l_abr ben_acty_base_rt_f.acty_base_rt_id%type;
531 l_ovn number := 1;
532 l_effective_start_date date ;
533 l_effective_end_date date ;
534 l_effective_date date;
535 l_business_group_id hr_all_organization_units.business_group_id%type;
536 l_dt_mode varchar2(30);
537 --
538 --
539 l_created_by ben_pl_f.created_by%TYPE;
540 l_last_updated_by ben_pl_f.last_updated_by%TYPE;
541 l_creation_date ben_pl_f.creation_date%TYPE;
542 l_last_update_date ben_pl_f.last_update_date%TYPE;
543 l_last_update_login ben_pl_f.last_update_login%TYPE;
544 --
545 -- Create a abr corresponding to Criteria rate defn in Rate matrix.
546 --
547 Cursor csr_plan is
548 Select rate_matrix_node_id, pl_id from pqh_rate_matrix_nodes
549 Where short_code = p_node_short_code
550 and business_group_id = l_business_group_id;
551 --
552 Cursor csr_plan_name is
553 select short_code
554 From ben_pl_f
555 Where pl_id = l_pl_id
556 and l_effective_date between effective_start_date and effective_end_date
557 and business_group_id = l_business_group_id;
558
559 --
560 Cursor csr_crd is
561 Select criteria_rate_defn_id,short_name
562 From pqh_criteria_rate_defn_vl
563 Where short_name = p_crit_rt_defn_short_code
564 and business_group_id = l_business_group_id;
565 --
566 -- Select activity base rate corresponding to the criteria rate defn in
567 -- rate matrix.
568 --
569 Cursor csr_abr is
570 select acty_base_rt_id
571 From ben_acty_base_rt_f
572 Where acty_typ_cd = 'RBC'
573 and l_effective_date between effective_start_date and effective_end_date
574 and business_group_id = l_business_group_id
575 and pl_id = l_pl_id
576 and mapping_table_name = 'PQH_CRITERIA_RATE_DEFN'
577 and mapping_table_pk_id = l_crd_id;
578 --
579 Cursor csr_rmr is
580 Select rate_matrix_rate_id ,object_version_number
581 From pqh_rate_matrix_rates_f
582 Where criteria_rate_defn_id = l_crd_id
583 and rate_matrix_node_id = l_rate_matrix_node_id
584 and l_effective_date between effective_start_date and effective_end_date
585 and business_group_id = l_business_group_id;
586
587 Begin
588 --
589 -- populate WHO columns
590 --
591 /**
592 if p_owner = 'SEED' then
593 l_created_by := 1;
594 l_last_updated_by := 1;
595 else
596 l_created_by := 0;
597 l_last_updated_by := 0;
598 end if;
599 **/
600 l_last_updated_by := fnd_load_util.owner_id(p_owner);
601 l_created_by := fnd_load_util.owner_id(p_owner);
602
603
604 l_creation_date := sysdate;
605 l_last_update_date := sysdate;
606 l_last_update_login := 0;
607 l_effective_date := nvl(to_date(p_effective_start_date,'DD/MM/YYYY'),sysdate);
608 --
609 l_business_group_id := fnd_global.PER_BUSINESS_GROUP_ID ;
610 --
611 Open csr_plan;
612 Fetch csr_plan into l_rate_matrix_node_id, l_pl_id;
613 Close csr_plan;
614
615 Open csr_plan_name;
616 Fetch csr_plan_name into l_pl_name;
617 Close csr_plan_name;
618
619
620 Open csr_crd;
621 Fetch csr_crd into l_crd_id,l_crd_name;
622 Close csr_crd;
623
624 Open csr_rmr;
625 Fetch csr_rmr into l_rt_matrix_rate_id,l_ovn;
626 Close csr_rmr;
627
628 -- No Rate matrix rate exists
629 --
630 If l_rt_matrix_rate_id is null then
631
632 Open csr_abr;
633 Fetch csr_abr into l_abr;
634 Close csr_abr;
635 --
636 If l_abr is null then
637 --
638 BEN_ACTY_BASE_RATE_API.CREATE_ACTY_BASE_RATE(
639 P_EFFECTIVE_DATE => l_effective_date
640 ,p_acty_base_rt_id => l_abr
641 ,p_effective_start_date => l_effective_start_date
642 ,p_effective_end_date => l_effective_end_date
643 ,P_BUSINESS_GROUP_ID => l_business_group_id
644 ,P_ACTY_BASE_RT_STAT_CD => 'A'
645 ,P_ACTY_TYP_CD => 'RBC'
646 ,P_NAME => l_pl_name||' - '||l_crd_name
647 ,P_PL_ID => l_pl_id
648 ,P_RT_MLT_CD => 'NSVU'
649 ,P_ELE_RQD_FLAG => 'N'
650 ,P_MAPPING_TABLE_NAME => 'PQH_CRITERIA_RATE_DEFN'
651 ,P_MAPPING_TABLE_PK_ID => l_crd_id
652 ,p_object_version_number => l_ovn
653 );
654 --
655 End if;
656 --
657 /**
658 l_ovn := 1;
659 Insert into pqh_rate_matrix_rates_f
660 (RATE_MATRIX_RATE_ID,
661 EFFECTIVE_START_DATE,
662 EFFECTIVE_END_DATE,
663 RATE_MATRIX_NODE_ID,
664 CRITERIA_RATE_DEFN_ID,
665 MIN_RATE_VALUE,
666 MAX_RATE_VALUE,
667 MID_RATE_VALUE,
668 RATE_VALUE,
669 BUSINESS_GROUP_ID,
670 LEGISLATION_CODE,
671 CREATED_BY ,
672 CREATION_DATE ,
673 LAST_UPDATED_BY ,
674 LAST_UPDATE_DATE ,
675 LAST_UPDATE_LOGIN ,
676 OBJECT_VERSION_NUMBER
677 )
678 Values
679 (pqh_rate_matrix_rates_s.nextval,
680 l_effective_date,
681 to_date('31/12/4712','dd/mm/yyyy'),
682 l_rate_matrix_node_id,
683 l_crd_id,
684 p_min_rate_value,
685 p_max_rate_value,
686 p_mid_rate_value,
687 p_rate_value,
688 l_business_group_id,
689 p_legislation_code,
690 l_created_by,
691 l_creation_date,
692 l_last_updated_by,
693 l_last_update_date,
694 l_last_update_login,
695 l_ovn);
696 **/
697
698 --
699 -- Create Rate matrix Rates.
700 --
701 PQH_RATE_MATRIX_RATES_API.create_rate_matrix_rate(
702 p_effective_date => l_effective_date
703 ,p_business_group_id => l_business_group_id
704 ,p_rate_matrix_rate_id => l_rt_matrix_rate_id
705 ,p_EFFECTIVE_START_DATE => l_effective_start_date
706 ,p_EFFECTIVE_END_DATE => l_effective_end_date
707 ,p_RATE_MATRIX_NODE_ID => l_rate_matrix_node_id
708 ,p_CRITERIA_RATE_DEFN_ID => l_crd_id
709 ,p_MIN_RATE_VALUE => p_min_rate_value
710 ,p_MAX_RATE_VALUE => p_max_rate_value
711 ,p_MID_RATE_VALUE => p_mid_rate_value
712 ,p_RATE_VALUE => p_rate_value
713 ,p_object_version_number => l_ovn
714 );
715
716 Else
717 l_dt_mode := pqh_gsp_stage_to_ben.get_update_mode('PQH_RATE_MATRIX_RATES_F','RATE_MATRIX_RATE_ID', l_rt_matrix_rate_id, l_effective_date) ;
718
719 PQH_RATE_MATRIX_RATES_API.update_rate_matrix_rate(
720 p_effective_date => l_effective_date
721 ,p_business_group_id => l_business_group_id
722 ,p_rate_matrix_rate_id => l_rt_matrix_rate_id
723 ,p_EFFECTIVE_START_DATE => l_effective_start_date
724 ,p_EFFECTIVE_END_DATE => l_effective_end_date
725 ,p_RATE_MATRIX_NODE_ID => l_rate_matrix_node_id
726 ,p_CRITERIA_RATE_DEFN_ID => l_crd_id
727 ,p_MIN_RATE_VALUE => p_min_rate_value
728 ,p_MAX_RATE_VALUE => p_max_rate_value
729 ,p_MID_RATE_VALUE => p_mid_rate_value
730 ,p_RATE_VALUE => p_rate_value
731 ,p_datetrack_mode => l_dt_mode
732 ,p_object_version_number => l_ovn
733 );
734
735 End if;
736 --
737 End;
738 --
739 --
740 -- ----------------------------------- Load Criteria Rate Defn -----------------------------------------
741 Procedure load_crd_seed_row
742 (p_upload_mode in varchar2
743 ,p_name in varchar2
744 ,p_short_name in varchar2
745 ,p_uom in varchar2
746 ,p_currency_code in varchar2 default null
747 ,p_reference_period_cd in varchar2 default null
748 ,p_define_max_rate_flag in varchar2 default null
749 ,p_define_min_rate_flag in varchar2 default null
750 ,p_define_mid_rate_flag in varchar2 default null
751 ,p_define_std_rate_flag in varchar2 default null
752 ,p_rate_calc_cd in varchar2
753 ,p_preferential_rate_cd in varchar2
754 ,p_rounding_cd in varchar2 default null
755 ,p_legislation_code in varchar2 default null
756 ,p_owner in varchar2 default null
757 ) is
758 --
759 Begin
760 if (p_upload_mode = 'NLS') then
761 pqh_crl_upd.translate_row (
762 p_short_name => p_short_name,
763 p_name => p_name ,
764 p_owner => p_owner);
765 else
766 pqh_rbc_load_objects.load_crd_row
767 (
768 p_name => p_name
769 ,p_short_name => p_short_name
770 ,p_uom => p_uom
771 ,p_currency_code => p_currency_code
772 ,p_reference_period_cd => p_reference_period_cd
773 ,p_define_max_rate_flag => p_define_max_rate_flag
774 ,p_define_min_rate_flag => p_define_min_rate_flag
775 ,p_define_mid_rate_flag => p_define_mid_rate_flag
776 ,p_define_std_rate_flag => p_define_std_rate_flag
777 ,p_rate_calc_cd => p_rate_calc_cd
778 ,p_preferential_rate_cd => p_preferential_rate_cd
779 ,p_rounding_cd => p_rounding_cd
780 ,p_legislation_code => p_legislation_code
781 ,p_owner => p_owner );
782 end if;
783
784 End;
785 --
786 Procedure load_crd_row
787 (p_name in varchar2
788 ,p_short_name in varchar2
789 ,p_uom in varchar2
790 ,p_currency_code in varchar2 default null
791 ,p_reference_period_cd in varchar2 default null
792 ,p_define_max_rate_flag in varchar2 default null
793 ,p_define_min_rate_flag in varchar2 default null
794 ,p_define_mid_rate_flag in varchar2 default null
795 ,p_define_std_rate_flag in varchar2 default null
796 ,p_rate_calc_cd in varchar2
797 --,p_rate_calc_rule in varchar2 default null
798 ,p_preferential_rate_cd in varchar2
799 --,p_preferential_rate_rule in varchar2 default null
800 ,p_rounding_cd in varchar2 default null
801 --,p_rounding_rule in varchar2
802 ,p_legislation_code in varchar2 default null
803 ,p_owner in varchar2 default null
804 ) is
805 --
806 l_criteria_rate_defn_id pqh_criteria_rate_defn.criteria_rate_defn_id%type ;
807 l_ovn number := 1;
808 l_effective_date date;
809 l_business_group_id hr_all_organization_units.business_group_id%type;
810 --
811 --
812 l_created_by pqh_criteria_rate_defn.created_by%TYPE;
813 l_last_updated_by pqh_criteria_rate_defn.last_updated_by%TYPE;
814 l_creation_date pqh_criteria_rate_defn.creation_date%TYPE;
815 l_last_update_date pqh_criteria_rate_defn.last_update_date%TYPE;
816 l_last_update_login pqh_criteria_rate_defn.last_update_login%TYPE;
817 --
818 l_language varchar2(30) ;
819
820 Cursor c1 is select userenv('LANG') from dual ;
821 --
822 -- Check if the criteria rate defn exists
823 --
824 Cursor csr_crd is
825 select criteria_rate_defn_id from pqh_criteria_rate_defn
826 where short_name = p_short_name
827 and business_group_id = l_business_group_id;
828 --
829 Cursor csr_crd_seq is
830 Select pqh_criteria_rate_defn_s.nextval
831 From dual;
832 Begin
833 --
834 -- populate WHO columns
835 --
836 /**
837 if p_owner = 'SEED' then
838 l_created_by := 1;
839 l_last_updated_by := 1;
840 else
841 l_created_by := 0;
842 l_last_updated_by := 0;
843 end if;
844 **/
845 l_last_updated_by := fnd_load_util.owner_id(p_owner);
846 l_created_by := fnd_load_util.owner_id(p_owner);
847
848
849 l_creation_date := sysdate;
850 l_last_update_date := sysdate;
851 l_last_update_login := 0;
852 --
853 l_business_group_id := fnd_global.PER_BUSINESS_GROUP_ID ;
854 --
855 open c1;
856 fetch c1 into l_language ;
857 close c1;
858 --
859 Open csr_crd;
860 Fetch csr_crd into l_criteria_rate_defn_id;
861 Close csr_crd;
862
863 -- No plan exists corresponding to Rate matrix short code
864 --
865 If l_criteria_rate_defn_id is not null then
866 Update pqh_criteria_rate_defn
867 set
868 short_name = p_short_name
869 ,uom = p_uom
870 ,currency_code = p_currency_code
871 ,reference_period_cd = p_reference_period_cd
872 ,define_max_rate_flag = p_define_max_rate_flag
873 ,define_min_rate_flag = p_define_min_rate_flag
874 ,define_mid_rate_flag = p_define_mid_rate_flag
875 ,define_std_rate_flag = p_define_std_rate_flag
876 ,rate_calc_cd = p_rate_calc_cd
877 --,rate_calc_rule = p_rate_calc_rule
878 ,preferential_rate_cd = p_preferential_rate_cd
879 --,preferential_rate_rule = p_preferential_rate_rule
880 ,rounding_cd = p_rounding_cd
881 --,rounding_rule = p_rounding_rule
882 ,business_group_id = l_business_group_id
883 ,legislation_code = p_legislation_code
884 ,last_updated_by = l_last_updated_by
885 ,last_update_date = l_last_update_date
886 ,last_update_login = l_last_update_login
887 Where criteria_rate_defn_id = l_criteria_rate_defn_id;
888
889 if (sql%found) then
890
891 UPDATE pqh_criteria_rate_defn_tl
892 SET name = p_name,
893 last_updated_by = l_last_updated_by,
894 last_update_date = l_last_update_date,
895 last_update_login = l_last_update_login,
896 source_lang = userenv('LANG')
897 WHERE criteria_rate_defn_id = l_criteria_rate_defn_id
898 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
899
900 If (sql%notfound) then
901 -- no row in TL table so insert row
902
903 --
904 insert into pqh_criteria_rate_defn_tl
905 ( criteria_rate_defn_id,
906 name,
907 language,
908 source_lang,
909 created_by,
910 creation_date,
911 last_updated_by,
912 last_update_date ,
913 last_update_login
914 )
915 Select
916 l_criteria_rate_defn_id,
917 p_name,
918 l.language_code,
919 l_language ,
920 l_created_by,
921 l_creation_date,
922 l_last_updated_by,
923 l_last_update_date,
924 l_last_update_login
925 from fnd_languages l
926 where l.installed_flag in ('I','B')
927 and not exists (select null
928 from pqh_criteria_rate_defn_tl crl
929 where crl.criteria_rate_defn_id = l_criteria_rate_defn_id
930 and crl.language = l.language_code );
931 --
932 End if;
933 --
934 end if; -- sql%found for main table
935
936
937 Else
938 --
939 Open csr_crd_seq;
940 Fetch csr_crd_seq into l_criteria_rate_defn_id;
941 Close csr_crd_seq;
942 --
943 Insert into pqh_criteria_rate_defn
944 (criteria_rate_defn_id
945 ,short_name
946 ,uom
947 ,currency_code
948 ,reference_period_cd
949 ,define_max_rate_flag
950 ,define_min_rate_flag
951 ,define_mid_rate_flag
952 ,define_std_rate_flag
953 ,rate_calc_cd
954 -- ,rate_calc_rule
955 ,preferential_rate_cd
956 -- ,preferential_rate_rule
957 ,rounding_cd
958 -- ,rounding_rule
959 ,business_group_id
960 ,legislation_code
961 ,created_by
962 ,creation_date
963 ,last_updated_by
964 ,last_update_date
965 ,last_update_login
966 ,object_version_number
967 )
968 Values
969 (l_criteria_rate_defn_id
970 ,p_short_name
971 ,p_uom
972 ,p_currency_code
973 ,p_reference_period_cd
974 ,p_define_max_rate_flag
975 ,p_define_min_rate_flag
976 ,p_define_mid_rate_flag
977 ,p_define_std_rate_flag
978 ,p_rate_calc_cd
979 -- ,p_rate_calc_rule
980 ,p_preferential_rate_cd
981 -- ,p_preferential_rate_rule
982 ,p_rounding_cd
983 -- ,p_rounding_rule
984 ,l_business_group_id
985 ,p_legislation_code
986 ,l_created_by
987 ,l_creation_date
988 ,l_last_updated_by
989 ,l_last_update_date
990 ,l_last_update_login
991 ,l_ovn
992 );
993
994 insert into pqh_criteria_rate_defn_tl
995 ( criteria_rate_defn_id,
996 name,
997 language,
998 source_lang,
999 created_by,
1000 creation_date,
1001 last_updated_by,
1002 last_update_date ,
1003 last_update_login
1004 )
1005 Select
1006 l_criteria_rate_defn_id,
1007 p_name,
1008 l.language_code,
1009 l_language ,
1010 l_created_by,
1011 l_creation_date,
1012 l_last_updated_by,
1013 l_last_update_date,
1014 l_last_update_login
1015 from fnd_languages l
1016 where l.installed_flag in ('I','B')
1017 and not exists (select null
1018 from pqh_criteria_rate_defn_tl crl
1019 where crl.criteria_rate_defn_id = l_criteria_rate_defn_id
1020 and crl.language = l.language_code );
1021
1022 End if;
1023 --
1024 End load_crd_row;
1025 --
1026 -- Load row for new tables
1027 --
1028 Procedure load_crf_row
1029 (
1030 p_crit_rt_defn_short_name in VARCHAR2
1031 ,p_parent_crit_rt_def_name in VARCHAR2
1032 ,p_owner in VARCHAR2
1033 ,p_parent_rate_matrix_code in VARCHAR2
1034 ,p_legislation_code in VARCHAR2
1035 ,p_effective_start_date in varchar2
1036 ) is
1037
1038 l_criteria_rate_defn_id pqh_criteria_rate_defn.criteria_rate_defn_id%type ;
1039 l_parent_criteria_rate_defn_id pqh_criteria_rate_defn.criteria_rate_defn_id%type ;
1040 l_criteria_rate_factor_id pqh_criteria_rate_factors.criteria_rate_factor_id%type ;
1041 l_parent_rate_matrix_id pqh_criteria_rate_factors.parent_rate_matrix_id%type ;
1042
1043 l_effective_date Date;
1044 l_object_version_number number := 1;
1045 l_business_group_id hr_all_organization_units.business_group_id%type;
1046 --
1047 --
1048 l_created_by pqh_criteria_rate_factors.created_by%TYPE;
1049 l_last_updated_by pqh_criteria_rate_factors.last_updated_by%TYPE;
1050 l_creation_date pqh_criteria_rate_factors.creation_date%TYPE;
1051 l_last_update_date pqh_criteria_rate_factors.last_update_date%TYPE;
1052 l_last_update_login pqh_criteria_rate_factors.last_update_login%TYPE;
1053 --
1054 l_language varchar2(30) ;
1055
1056 Cursor c1 is select userenv('LANG') from dual ;
1057 --
1058 -- Check if the criteria rate defn exists
1059 --
1060 Cursor csr_crd is
1061 select criteria_rate_defn_id from pqh_criteria_rate_defn
1062 where short_name = p_crit_rt_defn_short_name
1063 and business_group_id = l_business_group_id;
1064 --
1065 Cursor csr_crdp is
1066 select criteria_rate_defn_id from pqh_criteria_rate_defn
1067 where short_name = p_parent_crit_rt_def_name
1068 and business_group_id = l_business_group_id;
1069
1070 Cursor csr_crf(crd_id in number,crdp_id in number)is
1071 select criteria_rate_factor_id from pqh_criteria_rate_factors
1072 where criteria_rate_defn_id = crd_id
1073 and parent_criteria_rate_defn_id = crdp_id
1074 and business_group_id = l_business_group_id;
1075 --
1076 Cursor csr_crf_seq is
1077 Select pqh_criteria_rate_factors_s.nextval
1078 From dual;
1079
1080 /* Cursor csr_prm is
1081 select pl_id from ben_pl_f
1082 where short_code = p_parent_rate_matrix_code
1083 and l_effective_date between effective_start_date and effective_end_date
1084 and business_group_id = l_business_group_id;
1085 */
1086
1087 Begin
1088 --
1089 -- populate WHO columns
1090 --
1091 /**
1092 if p_owner = 'SEED' then
1093 l_created_by := 1;
1094 l_last_updated_by := 1;
1095 else
1096 l_created_by := 0;
1097 l_last_updated_by := 0;
1098 end if;
1099 **/
1100 l_last_updated_by := fnd_load_util.owner_id(p_owner);
1101 l_created_by := fnd_load_util.owner_id(p_owner);
1102
1103
1104 l_creation_date := sysdate;
1105 l_last_update_date := sysdate;
1106 l_last_update_login := 0;
1107 --
1108
1109 -- l_effective_date := SYSDATE;
1110 l_effective_date := nvl(to_date(p_effective_start_date,'DD/MM/YYYY'),sysdate);
1111 -- TEST
1112 l_business_group_id := fnd_global.PER_BUSINESS_GROUP_ID ;
1113 --
1114 Open csr_crd;
1115 Fetch csr_crd into l_criteria_rate_defn_id;
1116 Close csr_crd;
1117
1118 Open csr_crdp;
1119 Fetch csr_crdp into l_parent_criteria_rate_defn_id;
1120 Close csr_crdp;
1121
1122 Open csr_crf(l_criteria_rate_defn_id,l_parent_criteria_rate_defn_id);
1123 Fetch csr_crf into l_criteria_rate_factor_id;
1124 Close csr_crf;
1125 /*
1126 Open csr_prm;
1127 Fetch csr_prm into l_parent_rate_matrix_id;
1128 Close csr_prm;
1129 */
1130 l_parent_rate_matrix_id := get_plan_id(p_parent_rate_matrix_code,l_effective_date,l_business_group_id);
1131
1132 if(l_criteria_rate_factor_id is not null) then
1133 update pqh_criteria_rate_factors
1134 set
1135 criteria_rate_defn_id = l_criteria_rate_defn_id
1136 ,parent_rate_matrix_id = l_parent_rate_matrix_id
1137 ,parent_criteria_rate_defn_id = l_parent_criteria_rate_defn_id
1138 ,business_group_id = l_business_group_id
1139 ,legislation_code = p_legislation_code
1140 where criteria_rate_factor_id = l_criteria_rate_factor_id;
1141
1142
1143 else
1144 Open csr_crf_seq;
1145 Fetch csr_crf_seq into l_criteria_rate_factor_id;
1146 Close csr_crf_seq;
1147
1148
1149 insert into pqh_criteria_rate_factors
1150 (criteria_rate_factor_id
1151 ,criteria_rate_defn_id
1152 ,parent_rate_matrix_id
1153 ,parent_criteria_rate_defn_id
1154 ,business_group_id
1155 ,legislation_code
1156 ,object_version_number
1157 )
1158 Values
1159 (l_criteria_rate_factor_id
1160 ,l_criteria_rate_defn_id
1161 ,l_parent_rate_matrix_id
1162 ,l_parent_criteria_rate_defn_id
1163 ,l_business_group_id
1164 ,p_legislation_code
1165 ,l_object_version_number
1166 );
1167
1168 end if;
1169
1170 end load_crf_row;
1171
1172 Procedure load_rfe_row
1173 (p_crit_rt_defn_short_name in VARCHAR2
1174 ,p_parent_crit_rt_def_name in VARCHAR2
1175 ,p_owner in VARCHAR2
1176 ,p_element_type_name in VARCHAR2
1177 ,p_rate_factor_val_record_tbl in VARCHAR2
1178 ,p_rate_factor_val_record_col in VARCHAR2
1179 ,p_legislation_code in VARCHAR2
1180 ,p_effective_start_date in VARCHAR2
1181 ) is
1182
1183 l_criteria_rate_defn_id pqh_criteria_rate_defn.criteria_rate_defn_id%type ;
1184 l_parent_criteria_rate_defn_id pqh_criteria_rate_defn.criteria_rate_defn_id%type ;
1185 l_criteria_rate_factor_id pqh_criteria_rate_factors.criteria_rate_factor_id%type ;
1186
1187 l_element_type_id pay_element_types_f.element_type_id%type;
1188 l_criteria_rate_element_id pqh_criteria_rate_elements.criteria_rate_element_id%type;
1189
1190 l_rate_factor_on_elmnt_id pqh_rate_factor_on_elmnts.rate_factor_on_elmnt_id%TYPE;
1191 l_rate_factor_val_record_col pqh_rate_factor_on_elmnts.rate_factor_val_record_col%type;
1192
1193 l_effective_date Date;
1194 l_object_version_number number := 1;
1195 l_business_group_id hr_all_organization_units.business_group_id%type;
1196 --
1197 --
1198 l_created_by pqh_rate_factor_on_elmnts.created_by%TYPE;
1199 l_last_updated_by pqh_rate_factor_on_elmnts.last_updated_by%TYPE;
1200 l_creation_date pqh_rate_factor_on_elmnts.creation_date%TYPE;
1201 l_last_update_date pqh_rate_factor_on_elmnts.last_update_date%TYPE;
1202 l_last_update_login pqh_rate_factor_on_elmnts.last_update_login%TYPE;
1203 --
1204 l_language varchar2(30) ;
1205
1206 Cursor c1 is select userenv('LANG') from dual ;
1207 --
1208 -- Check if the criteria rate defn exists
1209 --
1210 Cursor csr_crd is
1211 select criteria_rate_defn_id from pqh_criteria_rate_defn
1212 where short_name = p_crit_rt_defn_short_name
1213 and business_group_id = l_business_group_id;
1214 --
1215 Cursor csr_crdp is
1216 select criteria_rate_defn_id from pqh_criteria_rate_defn
1217 where short_name = p_parent_crit_rt_def_name
1218 and business_group_id = l_business_group_id;
1219
1220 Cursor csr_crf(crd_id in number,crdp_id in number)is
1221 select criteria_rate_factor_id from pqh_criteria_rate_factors
1222 where criteria_rate_defn_id = crd_id
1223 and parent_criteria_rate_defn_id = crdp_id
1224 and business_group_id = l_business_group_id;
1225
1226 Cursor csr_ele is
1227 select element_type_id from pay_element_types_f
1228 where element_name = p_element_type_name
1229 and l_effective_date between effective_start_date and effective_end_date
1230 and (business_group_id = l_business_group_id OR (legislation_code in
1231 (select legislation_code from per_business_groups_perf where business_group_id = l_business_group_id)
1232 and business_group_id is null));
1233 --
1234
1235 Cursor csr_cre(crd_id in number,ele_id in number)is
1236 select criteria_rate_element_id from pqh_criteria_rate_elements
1237 where criteria_rate_defn_id = crd_id
1238 and element_type_id = ele_id
1239 and business_group_id = l_business_group_id;
1240 --
1241
1242 Cursor csr_rfe(crf_id in number,cre_id in number)is
1243 select rate_factor_on_elmnt_id from pqh_rate_factor_on_elmnts
1244 where criteria_rate_factor_id = crf_id
1245 and criteria_rate_element_id = cre_id
1246 and business_group_id = l_business_group_id;
1247 --
1248 /* dont store id
1249 Cursor csr_rel_inp is
1250 select input_value_id from pay_input_values_f
1251 where name = p_rate_factor_val_record_col
1252 and element_type_id = l_element_type_id
1253 and l_effective_date between effective_start_date and effective_end_date
1254 and business_group_id = l_business_group_id;
1255 */
1256
1257 Cursor csr_rel_inp is
1258 select name from pay_input_values_f
1259 where name = p_rate_factor_val_record_col
1260 and element_type_id = l_element_type_id
1261 and l_effective_date between effective_start_date and effective_end_date;
1262
1263
1264 Cursor csr_rfe_seq is
1265 Select pqh_rate_factor_on_elmnts_s.nextval
1266 From dual;
1267
1268 Begin
1269 --
1270 -- populate WHO columns
1271 --
1272 /**
1273 if p_owner = 'SEED' then
1274 l_created_by := 1;
1275 l_last_updated_by := 1;
1276 else
1277 l_created_by := 0;
1278 l_last_updated_by := 0;
1279 end if;
1280 **/
1281 l_last_updated_by := fnd_load_util.owner_id(p_owner);
1282 l_created_by := fnd_load_util.owner_id(p_owner);
1283
1284
1285 l_creation_date := sysdate;
1286 l_last_update_date := sysdate;
1287 l_last_update_login := 0;
1288 --
1289 l_effective_date := nvl(to_date(p_effective_start_date,'DD/MM/YYYY'),sysdate);
1290 l_business_group_id := fnd_global.PER_BUSINESS_GROUP_ID ;
1291 --
1292 Open csr_crd;
1293 Fetch csr_crd into l_criteria_rate_defn_id;
1294 Close csr_crd;
1295
1296 Open csr_crdp;
1297 Fetch csr_crdp into l_parent_criteria_rate_defn_id;
1298 Close csr_crdp;
1299
1300 Open csr_crf(l_criteria_rate_defn_id,l_parent_criteria_rate_defn_id);
1301 Fetch csr_crf into l_criteria_rate_factor_id;
1302 Close csr_crf;
1303
1304 -- got l_criteria_rate_factor_id
1305
1306 Open csr_ele;
1307 Fetch csr_ele into l_element_type_id;
1308 Close csr_ele;
1309
1310 Open csr_cre(l_criteria_rate_defn_id,l_element_type_id);
1311 Fetch csr_cre into l_criteria_rate_element_id;
1312 Close csr_cre;
1313
1314 if (LOWER(p_rate_factor_val_record_tbl) = 'pay_element_entries_f') then
1315 l_rate_factor_val_record_col := p_rate_factor_val_record_col;
1316 else
1317 open csr_rel_inp;
1318 Fetch csr_rel_inp into l_rate_factor_val_record_col;
1319 close csr_rel_inp;
1320 end if;
1321
1322
1323 open csr_rfe(l_criteria_rate_factor_id,l_criteria_rate_element_id);
1324 Fetch csr_rfe into l_rate_factor_on_elmnt_id;
1325 close csr_rfe;
1326
1327
1328 if(l_rate_factor_on_elmnt_id is not null) then
1329 update pqh_rate_factor_on_elmnts
1330 set
1331 rate_factor_on_elmnt_id =l_rate_factor_on_elmnt_id
1332 ,criteria_rate_element_id =l_criteria_rate_element_id
1333 ,criteria_rate_factor_id =l_criteria_rate_factor_id
1334 ,rate_factor_val_record_tbl =p_rate_factor_val_record_tbl
1335 ,rate_factor_val_record_col =l_rate_factor_val_record_col
1336 ,business_group_id =l_business_group_id
1337 ,legislation_code =p_legislation_code
1338 where rate_factor_on_elmnt_id =l_rate_factor_on_elmnt_id;
1339
1340 else
1341 Open csr_rfe_seq;
1342 Fetch csr_rfe_seq into l_rate_factor_on_elmnt_id;
1343 Close csr_rfe_seq;
1344 insert into pqh_rate_factor_on_elmnts
1345 (rate_factor_on_elmnt_id
1346 ,criteria_rate_element_id
1347 ,criteria_rate_factor_id
1348 ,rate_factor_val_record_tbl
1349 ,rate_factor_val_record_col
1350 ,business_group_id
1351 ,legislation_code
1352 ,object_version_number
1353 )
1354 Values
1355 (l_rate_factor_on_elmnt_id
1356 ,l_criteria_rate_element_id
1357 ,l_criteria_rate_factor_id
1358 ,p_rate_factor_val_record_tbl
1359 ,l_rate_factor_val_record_col
1360 ,l_business_group_id
1361 ,p_legislation_code
1362 ,l_object_version_number
1363 );
1364 end if;
1365
1366 end load_rfe_row;
1367
1368
1369 Procedure load_cre_row
1370 (p_crit_rt_defn_short_name in VARCHAR2
1371 ,p_element_type_name in VARCHAR2
1372 ,p_input_value_name in VARCHAR2
1373 ,p_owner in VARCHAR2
1374 ,p_legislation_code in VARCHAR2
1375 ,p_effective_start_date in VARCHAR2
1376 ) is
1377
1378 l_criteria_rate_defn_id pqh_criteria_rate_defn.criteria_rate_defn_id%type ;
1379 l_element_type_id pay_element_types_f.element_type_id%type;
1380 l_input_value_id pay_input_values_f.input_value_id%type;
1381 l_criteria_rate_element_id pqh_criteria_rate_elements.criteria_rate_element_id%type;
1382 l_effective_date Date;
1383 l_object_version_number number := 1;
1384 l_business_group_id hr_all_organization_units.business_group_id%type;
1385 --
1386 --
1387 l_created_by pqh_criteria_rate_elements.created_by%TYPE;
1388 l_last_updated_by pqh_criteria_rate_elements.last_updated_by%TYPE;
1389 l_creation_date pqh_criteria_rate_elements.creation_date%TYPE;
1390 l_last_update_date pqh_criteria_rate_elements.last_update_date%TYPE;
1391 l_last_update_login pqh_criteria_rate_elements.last_update_login%TYPE;
1392 --
1393 l_language varchar2(30) ;
1394
1395 Cursor csr_crd is
1396 select criteria_rate_defn_id from pqh_criteria_rate_defn
1397 where short_name = p_crit_rt_defn_short_name
1398 and business_group_id = l_business_group_id;
1399 --
1400
1401 Cursor csr_ele is
1402 select element_type_id from pay_element_types_f
1403 where element_name = p_element_type_name
1404 and l_effective_date between effective_start_date and effective_end_date
1405 and (business_group_id = l_business_group_id OR (legislation_code in
1406 (select legislation_code from per_business_groups_perf where business_group_id = l_business_group_id)
1407 and business_group_id is null));
1408 --
1409
1410 Cursor csr_inp is
1411 select input_value_id from pay_input_values_f
1412 where name = p_input_value_name
1413 and element_type_id = l_element_type_id
1414 and l_effective_date between effective_start_date and effective_end_date
1415 and (business_group_id = l_business_group_id OR (legislation_code in
1416 (select legislation_code from per_business_groups_perf where business_group_id = l_business_group_id)
1417 and business_group_id is null));
1418 --
1419
1420 Cursor csr_cre(crd_id in number,ele_id in number,inp_id in number)is
1421 select criteria_rate_element_id from pqh_criteria_rate_elements
1422 where criteria_rate_defn_id = crd_id
1423 and element_type_id = ele_id
1424 and input_value_id = inp_id
1425 and business_group_id = l_business_group_id;
1426 --
1427 Cursor csr_cre_seq is
1428 Select pqh_criteria_rate_elements_s.nextval
1429 From dual;
1430
1431
1432 Cursor c1 is select userenv('LANG') from dual ;
1433
1434 Begin
1435 --
1436 -- populate WHO columns
1437 --
1438 /**
1439 if p_owner = 'SEED' then
1440 l_created_by := 1;
1441 l_last_updated_by := 1;
1442 else
1443 l_created_by := 0;
1444 l_last_updated_by := 0;
1445 end if;
1446 **/
1447 l_last_updated_by := fnd_load_util.owner_id(p_owner);
1448 l_created_by := fnd_load_util.owner_id(p_owner);
1449
1450
1451 l_creation_date := sysdate;
1452 l_last_update_date := sysdate;
1453 l_last_update_login := 0;
1454 --
1455 l_business_group_id := fnd_global.PER_BUSINESS_GROUP_ID ;
1456 --
1457 -- l_effective_date := SYSDATE;
1458 l_effective_date := nvl(to_date(p_effective_start_date,'DD/MM/YYYY'),sysdate);
1459 -- TEST
1460
1461 Open csr_crd;
1462 Fetch csr_crd into l_criteria_rate_defn_id;
1463 Close csr_crd;
1464
1465 Open csr_ele;
1466 Fetch csr_ele into l_element_type_id;
1467 Close csr_ele;
1468
1469 Open csr_inp;
1470 Fetch csr_inp into l_input_value_id;
1471 Close csr_inp;
1472
1473 Open csr_cre(l_criteria_rate_defn_id,l_element_type_id,l_input_value_id);
1474 Fetch csr_cre into l_criteria_rate_element_id;
1475 Close csr_cre;
1476
1477
1478 if l_criteria_rate_element_id is not null then
1479 update pqh_criteria_rate_elements
1480 set
1481 criteria_rate_element_id = l_criteria_rate_element_id
1482 ,criteria_rate_defn_id = l_criteria_rate_defn_id
1483 ,element_type_id = l_element_type_id
1484 ,input_value_id = l_input_value_id
1485 ,business_group_id = l_business_group_id
1486 ,legislation_code = p_legislation_code
1487 ,object_version_number = l_object_version_number
1488 where criteria_rate_element_id = l_criteria_rate_element_id;
1489 else
1490 Open csr_cre_seq;
1491 Fetch csr_cre_seq into l_criteria_rate_element_id;
1492 Close csr_cre_seq;
1493
1494 insert into pqh_criteria_rate_elements
1495 (criteria_rate_element_id
1496 ,criteria_rate_defn_id
1497 ,element_type_id
1498 ,input_value_id
1499 ,business_group_id
1500 ,legislation_code
1501 ,object_version_number
1502 )
1503 Values
1504 (l_criteria_rate_element_id
1505 ,l_criteria_rate_defn_id
1506 ,l_element_type_id
1507 ,l_input_value_id
1508 ,l_business_group_id
1509 ,p_legislation_code
1510 ,l_object_version_number
1511 );
1512 end if;
1513
1514 end load_cre_row;
1515
1516
1517 Procedure load_rer_row
1518 (
1519 p_crit_rt_defn_short_name in VARCHAR2
1520 ,p_element_type_name in VARCHAR2
1521 ,p_owner in VARCHAR2
1522 ,p_relation_type_code in VARCHAR2
1523 ,p_rel_element_name in VARCHAR2
1524 ,p_rel_input_val_name in VARCHAR2
1525 ,p_legislation_code in VARCHAR2
1526 ,p_effective_start_date in varchar2
1527 ) is
1528 --
1529 l_criteria_rate_defn_id pqh_criteria_rate_defn.criteria_rate_defn_id%type ;
1530 l_element_type_id pay_element_types_f.element_type_id%type;
1531 l_criteria_rate_element_id pqh_criteria_rate_elements.criteria_rate_element_id%type;
1532 l_rel_element_type_id pqh_rate_element_relations.rel_element_type_id%type;
1533 l_rel_input_value_id pqh_rate_element_relations.rel_input_value_id%type;
1534 l_relation_type_cd pqh_rate_element_relations.relation_type_cd%type;
1535 l_rate_element_relation_id pqh_rate_element_relations.rate_element_relation_id%type;
1536 l_effective_date Date;
1537
1538 l_object_version_number number := 1;
1539 l_business_group_id hr_all_organization_units.business_group_id%type;
1540 --
1541 --
1542 l_created_by pqh_rate_element_relations.created_by%TYPE;
1543 l_last_updated_by pqh_rate_element_relations.last_updated_by%TYPE;
1544 l_creation_date pqh_rate_element_relations.creation_date%TYPE;
1545 l_last_update_date pqh_rate_element_relations.last_update_date%TYPE;
1546 l_last_update_login pqh_rate_element_relations.last_update_login%TYPE;
1547 --
1548 l_language varchar2(30) ;
1549
1550
1551
1552 Cursor c1 is select userenv('LANG') from dual ;
1553
1554 Cursor csr_crd is
1555 select criteria_rate_defn_id from pqh_criteria_rate_defn
1556 where short_name = p_crit_rt_defn_short_name
1557 and business_group_id = l_business_group_id;
1558 --
1559
1560 Cursor csr_ele is
1561 select element_type_id from pay_element_types_f
1562 where element_name = p_element_type_name
1563 and l_effective_date between effective_start_date and effective_end_date
1564 and (business_group_id = l_business_group_id OR (legislation_code in
1565 (select legislation_code from per_business_groups_perf where business_group_id = l_business_group_id)
1566 and business_group_id is null));
1567 --
1568
1569
1570 Cursor csr_cre(crd_id in number,ele_id in number)is
1571 select criteria_rate_element_id from pqh_criteria_rate_elements
1572 where criteria_rate_defn_id = crd_id
1573 and element_type_id = ele_id
1574 and business_group_id = l_business_group_id;
1575
1576 --
1577 --
1578 Cursor csr_rel_ele is
1579 select element_type_id from pay_element_types_f
1580 where element_name = p_rel_element_name
1581 and l_effective_date between effective_start_date and effective_end_date
1582 and business_group_id = l_business_group_id;
1583
1584
1585
1586 Cursor csr_rel_inp is
1587 select input_value_id from pay_input_values_f
1588 where name = p_rel_input_val_name
1589 and element_type_id = l_rel_element_type_id
1590 and l_effective_date between effective_start_date and effective_end_date
1591 and business_group_id = l_business_group_id;
1592 --
1593
1594
1595 Cursor csr_rer(cre_id in number,rel_ele in number)is
1596 select rate_element_relation_id from pqh_rate_element_relations
1597 where criteria_rate_element_id = cre_id
1598 and relation_type_cd = p_relation_type_code
1599 and rel_element_type_id = rel_ele
1600 and business_group_id = l_business_group_id;
1601 --
1602 --
1603 Cursor csr_rer_seq is
1604 Select pqh_rate_element_relations_s.nextval
1605 From dual;
1606
1607
1608 Begin
1609 --
1610 -- populate WHO columns
1611 --
1612 /**
1613 if p_owner = 'SEED' then
1614 l_created_by := 1;
1615 l_last_updated_by := 1;
1616 else
1617 l_created_by := 0;
1618 l_last_updated_by := 0;
1619 end if;
1620 **/
1621 l_last_updated_by := fnd_load_util.owner_id(p_owner);
1622 l_created_by := fnd_load_util.owner_id(p_owner);
1623
1624
1625 l_creation_date := sysdate;
1626 l_last_update_date := sysdate;
1627 l_last_update_login := 0;
1628 --
1629 l_business_group_id := fnd_global.PER_BUSINESS_GROUP_ID ;
1630 --
1631 l_effective_date := nvl(to_date(p_effective_start_date,'DD/MM/YYYY'),sysdate);
1632 -- TEST
1633 -- l_effective_date := sysdate;
1634
1635 Open csr_crd;
1636 Fetch csr_crd into l_criteria_rate_defn_id;
1637 Close csr_crd;
1638
1639 Open csr_ele;
1640 Fetch csr_ele into l_element_type_id;
1641 Close csr_ele;
1642
1643 Open csr_cre(l_criteria_rate_defn_id,l_element_type_id);
1644 Fetch csr_cre into l_criteria_rate_element_id;
1645 Close csr_cre;
1646
1647 open csr_rel_ele;
1648 Fetch csr_rel_ele into l_rel_element_type_id;
1649 close csr_rel_ele;
1650
1651 if p_rel_input_val_name is not null then
1652
1653 open csr_rel_inp;
1654 Fetch csr_rel_inp into l_rel_input_value_id;
1655 Close csr_rel_inp;
1656
1657 end if;
1658
1659 open csr_rer(l_criteria_rate_element_id,l_rel_element_type_id);
1660 Fetch csr_rer into l_rate_element_relation_id;
1661 close csr_rer;
1662
1663 if l_rate_element_relation_id is not null then
1664
1665 update pqh_rate_element_relations
1666 set
1667 rate_element_relation_id = l_rate_element_relation_id
1668 ,criteria_rate_element_id = l_criteria_rate_element_id
1669 ,relation_type_cd = p_relation_type_code
1670 ,rel_element_type_id = l_rel_element_type_id
1671 ,rel_input_value_id = l_rel_input_value_id
1672 ,business_group_id = l_business_group_id
1673 ,legislation_code = p_legislation_code
1674 where rate_element_relation_id = l_rate_element_relation_id;
1675
1676
1677 else
1678 Open csr_rer_seq;
1679 Fetch csr_rer_seq into l_rate_element_relation_id;
1680 Close csr_rer_seq;
1681
1682 insert into pqh_rate_element_relations
1683 (rate_element_relation_id
1684 ,criteria_rate_element_id
1685 ,relation_type_cd
1686 ,rel_element_type_id
1687 ,rel_input_value_id
1688 ,business_group_id
1689 ,legislation_code
1690 ,object_version_number
1691 )
1692 Values
1693 (l_rate_element_relation_id
1694 ,l_criteria_rate_element_id
1695 ,p_relation_type_code
1696 ,l_rel_element_type_id
1697 ,l_rel_input_value_id
1698 ,l_business_group_id
1699 ,p_legislation_code
1700 ,l_object_version_number
1701 );
1702
1703
1704 end if;
1705
1706
1707
1708 end load_rer_row;
1709
1710 Procedure download_rbc(
1711 errbuf out nocopy varchar2
1712 ,retcode out nocopy number
1713 ,p_loader_file in varchar2
1714 ,p_data_file in varchar2
1715 ,p_entity in varchar2
1716 ,p_crit_rate_defn_code in varchar2 default null
1717 ,p_rate_matrix_code in varchar2 default null
1718 ,p_effective_date in varchar2
1719 ,p_business_group_id in number
1720 ,p_validate in varchar2 default 'N'
1721 ) is
1722 --
1723
1724 l_proc varchar2(72) := 'download_rbc';
1725 --
1726 l_business_group_name varchar2(2000) := '' ;
1727 l_crit_defn_short_code varchar2(2000) := '' ;
1728 l_rate_matrix_short_code varchar2(2000) := '' ;
1729 l_effective_date varchar2(2000) := '' ;
1730 l_request_id number;
1731 l_validate varchar2(2000) ;
1732 begin
1733 --
1734 hr_utility.set_location('Entering:'|| l_proc, 10);
1735 hr_utility.set_location('p_loader_file '|| p_loader_file,10) ;
1736 hr_utility.set_location('p_data_file '|| p_data_file ,10) ;
1737 hr_utility.set_location('p_crit_rate_defn_code '|| p_crit_rate_defn_code,10) ;
1738 hr_utility.set_location('p_rate_matrix_code'||p_rate_matrix_code ,10) ;
1739 hr_utility.set_location('p_effective_date'||p_effective_date ,10) ;
1740 hr_utility.set_location('p_business_group_id'||p_business_group_id ,10) ;
1741 hr_utility.set_location('p_validate '|| p_validate ,10) ;
1742 --
1743 --
1744
1745 savepoint SUBMIT_RBC_DOWNLOAD;
1746 --
1747 hr_utility.set_location(l_proc, 20);
1748
1749 --
1750
1751 If p_crit_rate_defn_code is not null then
1752 l_crit_defn_short_code := 'CRIT_RT_DEFN_SHORT_NAME='||p_crit_rate_defn_code;
1753 End if;
1754
1755 If p_rate_matrix_code is not null then
1756 l_rate_matrix_short_code := 'PL_SHORT_CODE='||p_rate_matrix_code;
1757 End if;
1758
1759 l_effective_date := 'EFFECTIVE_DATE='|| p_effective_date ;
1760 l_business_group_name := 'BG_ID='|| to_char(p_business_group_id) ;
1761
1762
1763 hr_utility.set_location('Download ',20) ;
1764 l_request_id := fnd_request.submit_request
1765 (application => 'PQH'
1766 ,program => 'PQHRBCLD'
1767 ,description => NULL
1768 ,sub_request => FALSE
1769 ,argument1 => 'DOWNLOAD'
1770 ,argument2 => p_loader_file
1771 ,argument3 => p_data_file
1772 ,argument4 => p_entity
1773 ,argument5 => l_crit_defn_short_code
1774 ,argument6 => l_rate_matrix_short_code
1775 ,argument7 => l_effective_date
1776 ,argument8 => l_business_group_name
1777
1778 );
1779 --
1780 hr_utility.set_location(' Request id:'||to_char(l_request_id), 70);
1781 hr_utility.set_location(' Leaving:'||l_proc, 70);
1782 --
1783 exception
1784 --
1785 when others then
1786 --
1787 -- A validation or unexpected error has occured
1788 --
1789 ROLLBACK TO SUBMIT_RBC_DOWNLOAD;
1790 raise;
1791 --
1792 end download_rbc;
1793 --
1794 Procedure upload_rbc(
1795 errbuf out nocopy varchar2
1796 ,retcode out nocopy number
1797 ,p_loader_file in varchar2
1798 ,p_data_file in varchar2
1799 ,p_entity in varchar2
1800 ,p_crit_rate_defn_code in varchar2 default null
1801 ,p_rate_matrix_code in varchar2 default null
1802 ,p_validate in varchar2 default 'N'
1803 ) is
1804 --
1805
1806 l_proc varchar2(72) := 'UpLoad_rbc';
1807 --
1808 l_data_migrator_mode varchar2(10);
1809 l_crit_defn_short_code varchar2(2000) := '' ;
1810 l_rate_matrix_short_code varchar2(2000) := '' ;
1811 l_request_id number;
1812 l_validate varchar2(2000) ;
1813 begin
1814 --
1815 hr_utility.set_location('Entering:'|| l_proc, 10);
1816 hr_utility.set_location('p_loader_file '|| p_loader_file,10) ;
1817 hr_utility.set_location('p_data_file '|| p_data_file ,10) ;
1818 hr_utility.set_location('p_crit_rate_defn_code '|| p_crit_rate_defn_code,10) ;
1819 hr_utility.set_location('p_rate_matrix_code'||p_rate_matrix_code ,10) ;
1820 hr_utility.set_location('p_validate '|| p_validate ,10) ;
1821 --
1822 -- Setting g_data_migrator_mode = 'Y' , so that database triggers do not
1823 -- fire.
1824 --
1825 --l_data_migrator_mode := hr_general.g_data_migrator_mode ;
1826 --hr_general.g_data_migrator_mode := 'Y';
1827 --
1828 savepoint SUBMIT_RBC_UPLOAD;
1829 --
1830 hr_utility.set_location(l_proc, 20);
1831 --
1832
1833 If p_crit_rate_defn_code is not null then
1834 l_crit_defn_short_code := 'CRIT_RT_DEFN_SHORT_NAME='||p_crit_rate_defn_code;
1835 End if;
1836
1837 If p_rate_matrix_code is not null then
1838 l_rate_matrix_short_code := 'PL_SHORT_CODE='||p_rate_matrix_code;
1839 End if;
1840
1841 hr_utility.set_location('Upload ',20) ;
1842 l_request_id := fnd_request.submit_request
1843 (application => 'PQH'
1844 ,program => 'PQHRBCLD'
1845 ,description => NULL
1846 ,sub_request => FALSE
1847 ,argument1 => 'UPLOAD_PARTIAL'
1848 ,argument2 => p_loader_file
1849 ,argument3 => p_data_file
1850 ,argument4 => p_entity
1851 ,argument5 => l_crit_defn_short_code
1852 ,argument6 => l_rate_matrix_short_code
1853 );
1854 --
1855 --
1856 -- Re-setting g_data_migrator_mode to its previous value
1857 --
1858 --hr_general.g_data_migrator_mode := l_data_migrator_mode;
1859 --
1860 hr_utility.set_location(' Request id:'||to_char(l_request_id), 70);
1861 hr_utility.set_location(' Leaving:'||l_proc, 70);
1862 --
1863 exception
1864 --
1865 when others then
1866 --
1867 -- A validation or unexpected error has occured
1868 -- Re-setting g_data_migrator_mode to its previous value
1869 --
1870 --hr_general.g_data_migrator_mode := l_data_migrator_mode;
1871 --
1872 ROLLBACK TO SUBMIT_RBC_UPLOAD;
1873 raise;
1874 --
1875 end upload_rbc;
1876 --
1877 -- ----------------------------------------------------------------------------
1878 -- |-----------------------------< add_language >------------------------------|
1879 -- ----------------------------------------------------------------------------
1880 -- Procedure added as a fix for bug 5484366
1881
1882 Procedure ADD_LANGUAGE
1883 is
1884 begin
1885 delete from PQH_CRITERIA_RATE_DEFN_TL T
1886 where not exists
1887 (select NULL
1888 from PQH_CRITERIA_RATE_DEFN B
1889 where B.CRITERIA_RATE_DEFN_ID = T.CRITERIA_RATE_DEFN_ID
1890 );
1891
1892 update PQH_CRITERIA_RATE_DEFN_TL T set (
1893 NAME
1894 ) = (select
1895 B.NAME
1896 from PQH_CRITERIA_RATE_DEFN_TL B
1897 where B.CRITERIA_RATE_DEFN_ID = T.CRITERIA_RATE_DEFN_ID
1898 and B.LANGUAGE = T.SOURCE_LANG)
1899 where (
1900 T.CRITERIA_RATE_DEFN_ID,
1901 T.LANGUAGE
1902 ) in (select
1903 SUBT.CRITERIA_RATE_DEFN_ID,
1904 SUBT.LANGUAGE
1905 from PQH_CRITERIA_RATE_DEFN_TL SUBB, PQH_CRITERIA_RATE_DEFN_TL SUBT
1906 where SUBB.CRITERIA_RATE_DEFN_ID = SUBT.CRITERIA_RATE_DEFN_ID
1907 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1908 and (SUBB.NAME <> SUBT.NAME
1909 ));
1910
1911 insert into PQH_CRITERIA_RATE_DEFN_TL (
1912 CRITERIA_RATE_DEFN_ID,
1913 NAME,
1914 LAST_UPDATE_DATE,
1915 CREATION_DATE,
1916 CREATED_BY,
1917 LAST_UPDATE_LOGIN,
1918 LAST_UPDATED_BY,
1919 LANGUAGE,
1920 SOURCE_LANG
1921 ) select
1922 B.CRITERIA_RATE_DEFN_ID,
1923 B.NAME,
1924 B.LAST_UPDATE_DATE,
1925 B.CREATION_DATE,
1926 B.CREATED_BY,
1927 B.LAST_UPDATE_LOGIN,
1928 B.LAST_UPDATED_BY,
1929 L.LANGUAGE_CODE,
1930 B.SOURCE_LANG
1931 from PQH_CRITERIA_RATE_DEFN_TL B, FND_LANGUAGES L
1932 where L.INSTALLED_FLAG in ('I', 'B')
1933 and B.LANGUAGE = userenv('LANG')
1934 and not exists
1935 (select NULL
1936 from PQH_CRITERIA_RATE_DEFN_TL T
1937 where T.CRITERIA_RATE_DEFN_ID = B.CRITERIA_RATE_DEFN_ID
1938 and T.LANGUAGE = L.LANGUAGE_CODE);
1939 end ADD_LANGUAGE;
1940 -- --
1941 -- --
1942 End pqh_rbc_load_objects;