[Home] [Help]
6: -- ----------------------------------------------------------------------------
7: -- | Private Global Definitions |
8: -- ----------------------------------------------------------------------------
9: --
10: g_debug boolean := hr_utility.debug_enabled;
11: g_package varchar2(33) := ' ben_ext_fmt.'; -- Global package name
12: g_val_def valtabtyp := valtabtyp(null,null,null,null,null,null,null,null,null,null,
13: null,null,null,null,null,null,null,null,null,null,
14: null,null,null,null,null,null,null,null,null,null,
73: l_rslt_elmt varchar2(300) ;
74:
75: begin
76:
77: hr_utility.set_location('Entering:'||l_proc, 5);
78:
79:
80: open c_leg ;
81: fetch c_leg into l_LEGISLATION_CODE ;
89: ,p_legislation_code => l_LEGISLATION_CODE
90: ,p_defined_balance_id => p_defined_balance_id
91: ) ;
92:
93: hr_utility.set_location(' Exiting: ' || l_rslt_elmt || ' : ' ||l_proc, 15);
94: return l_rslt_elmt ;
95: end ;
96:
97:
106: val_name varchar2(250);
107: --
108: Begin
109: --
110: hr_utility.set_location('Entering:'||l_proc, 5);
111: --
112: select user_name into val_name
113: from fnd_user
114: where user_id = p_val_id;
112: select user_name into val_name
113: from fnd_user
114: where user_id = p_val_id;
115:
116: hr_utility.set_location(' Exiting:'||l_proc, 15);
117: return val_name;
118: --
119: --
120: Exception
119: --
120: Exception
121: When NO_DATA_FOUND Then
122: --
123: hr_utility.set_location(' Exiting:'||l_proc, 999);
124: --
125: return null;
126: End get_name;
127: --
138: l_err_message fnd_new_messages.message_text%type ;
139: l_proc varchar2(72) := g_package||'get_error_msg';
140: begin
141:
142: hr_utility.set_location('Entering'||l_proc, 5);
143: hr_utility.set_location('Error'||substr(p_err_name,1,100), 99.99);
144: fnd_message.set_name('BEN',p_err_name);
145: if P_err_no in ( 91888,92065,91924,91887,92313,92312,92678,92679 )
146: and p_token1 is not null then
139: l_proc varchar2(72) := g_package||'get_error_msg';
140: begin
141:
142: hr_utility.set_location('Entering'||l_proc, 5);
143: hr_utility.set_location('Error'||substr(p_err_name,1,100), 99.99);
144: fnd_message.set_name('BEN',p_err_name);
145: if P_err_no in ( 91888,92065,91924,91887,92313,92312,92678,92679 )
146: and p_token1 is not null then
147: fnd_message.set_token('DATA' , p_token1 ) ;
151: -- if any new error calls this then the totken is to be added here
152: -- fnd_message.set_token('FIELD','p_prvs_stat_cd');
153: -- fnd_message.set_token('TYPE','BEN_PER_IN_LER_STAT_CD');
154: l_err_message := fnd_message.get ;
155: hr_utility.set_location('message'||substr(l_err_message,1,100), 99.99);
156: hr_utility.set_location('Exiting'||l_proc, 5);
157: return (l_err_message) ;
158:
159: end ;
152: -- fnd_message.set_token('FIELD','p_prvs_stat_cd');
153: -- fnd_message.set_token('TYPE','BEN_PER_IN_LER_STAT_CD');
154: l_err_message := fnd_message.get ;
155: hr_utility.set_location('message'||substr(l_err_message,1,100), 99.99);
156: hr_utility.set_location('Exiting'||l_proc, 5);
157: return (l_err_message) ;
158:
159: end ;
160:
173: --
174: begin
175: --
176: --
177: hr_utility.set_location('Entering'||l_proc, 5);
178: --
179: if p_ext_data_elmt_typ = 'F' then
180: l_val := p_name;
181: else
182: l_val := p_id;
183: end if;
184: return(l_val);
185: --
186: hr_utility.set_location('Exiting:'||l_proc, 15);
187: --
188: end decode_setup;
189: --
190:
216: and val = nvl(p_value, ' ');
217: --
218: Begin
219: --
220: hr_utility.set_location('Entering:'||l_proc, 5);
221: hr_utility.set_location('code for decode ' ||p_value||' '|| p_ext_data_elmt_id ,936);
222: --
223: hr_utility.set_location('MONSTER defa '||p_default ,10);
224:
217: --
218: Begin
219: --
220: hr_utility.set_location('Entering:'||l_proc, 5);
221: hr_utility.set_location('code for decode ' ||p_value||' '|| p_ext_data_elmt_id ,936);
222: --
223: hr_utility.set_location('MONSTER defa '||p_default ,10);
224:
225: if p_value is not null then
219: --
220: hr_utility.set_location('Entering:'||l_proc, 5);
221: hr_utility.set_location('code for decode ' ||p_value||' '|| p_ext_data_elmt_id ,936);
222: --
223: hr_utility.set_location('MONSTER defa '||p_default ,10);
224:
225: if p_value is not null then
226: --
227: open c1;
288: else
289: --
290: if p_default is not null and upper(p_default) <> 'NULL' then
291: --
292: hr_utility.set_location(' retunr defaul :'||p_default, 15);
293: return(p_default);
294: --
295: else
296: --
299: end if;
300: --
301: end if;
302: --
303: hr_utility.set_location(' Exiting:'||l_proc, 15);
304: --
305: End apply_decode;
306:
307: --
332: --
333: l_rslt_elmt varchar2(4000) ;
334:
335: begin
336: hr_utility.set_location('Entering'||l_proc, 5);
337:
338: if p_one_char_substr = 'A' then
339: --
340: if p_two_char_substr in ('AC','AP') then
811:
812: END IF;
813: elsif p_two_char_substr = 'CW' then
814: --- CWB Elements
815: hr_utility.set_location(' cwb extract ' || p_two_char_substr || ' /' || p_short_name , 99 );
816: IF p_short_name = 'CWBLEN' then
817: l_rslt_elmt := ben_ext_person.g_CWB_Life_Event_Name ;
818: ELSIF p_short_name = 'CWBLEOD' then
819: l_rslt_elmt := apply_format_mask (ben_ext_person.g_CWB_Life_Event_Occurred_Date,p_frmt_mask_cd);
954: ELSIF p_short_name = 'CWBPF' then
955: l_rslt_elmt := ben_ext_person.g_CWB_Person_FREQUENCY ;
956: ELSIF p_short_name = 'CWBPFN' then
957: l_rslt_elmt := ben_ext_person.g_CWB_Person_FULL_NAME ;
958: hr_utility.set_location(' cwbfn ' || ben_ext_person.g_CWB_Person_FULL_NAME , 99 );
959: ELSIF p_short_name = 'CWBPGAF' then
960: l_rslt_elmt := ben_ext_person.g_CWB_Person_GRADE_ANN_FACTOR ;
961: ELSIF p_short_name = 'CWBPGC' then
962: l_rslt_elmt := ben_ext_person.g_CWB_Person_Grade_COMPARATIO ;
1590: l_rslt_elmt :=
1591: apply_format_mask(ben_ext_person.g_element_entry_eff_start_date,p_frmt_mask_cd);
1592: ELSIF p_short_name = 'ELVESDT' THEN
1593: l_rslt_elmt := apply_format_mask(ben_ext_person.g_element_eev_eff_strt_date,p_frmt_mask_cd);
1594: hr_utility.set_location('eeveffdate '||ben_ext_person.g_element_eev_eff_strt_date ,99);
1595:
1596: ELSIF p_short_name = 'ELVEEDT' THEN
1597: if ben_ext_person.g_element_eev_eff_end_date = hr_api.g_eot then
1598: l_rslt_elmt := null ;
1598: l_rslt_elmt := null ;
1599: else
1600: l_rslt_elmt := apply_format_mask(ben_ext_person.g_element_eev_eff_end_date,p_frmt_mask_cd);
1601: end if ;
1602: hr_utility.set_location('eeveffdate '||ben_ext_person.g_element_eev_eff_end_date ,99);
1603: ELSIF p_short_name = 'ELESTS' THEN
1604: l_rslt_elmt := ben_ext_person.g_elig_lfevt_status;
1605: ELSIF p_short_name = 'ELEVL' THEN
1606: -- checking whether numeric formating applicable
1736: to_char(ben_ext_person.g_employee_status_id));
1737: ELSIF p_short_name = 'EMPASID' then
1738: l_rslt_elmt := ben_ext_person.g_assignment_id ;
1739: ELSIF p_short_name = 'EMPASTY' then
1740: hr_utility.set_location(' EMPASTY ' || ben_ext_person.g_asg_type, 99 );
1741: l_rslt_elmt := ben_ext_person.g_asg_type;
1742: end if;
1743: elsif p_two_char_substr = 'EN' then
1744: IF p_short_name = 'ENDDT' THEN
2591: l_rslt_elmt :=
2592: apply_format_mask(ben_ext_person.g_er_cwb_worksheet_bdgt , p_frmt_mask_cd);
2593: --Enrollment Rate - CWB Worksheet Amount
2594: ELSIF p_short_name = 'PECWBWSAM' THEN
2595: hr_utility.set_location(' Worksheet Amount ' || ben_ext_person.g_er_cwb_worksheet_amt,991);
2596: l_rslt_elmt :=
2597: apply_format_mask(ben_ext_person.g_er_cwb_worksheet_amt , p_frmt_mask_cd);
2598: --Enrollment Rate - CWB Misc Rate 3
2599: ELSIF p_short_name = 'PECWBMSR3' THEN
2832: l_rslt_elmt := ben_ext_person.g_ptp_attr_10;
2833: end if;
2834: elsif p_two_char_substr = 'PM' then
2835: IF p_short_name = 'PMTPRAMT' THEN
2836: hr_utility.set_location(' pm ' || p_short_name ,5382 );
2837: l_rslt_elmt :=
2838: apply_format_mask(ben_ext_person.g_enrt_mntot_prem_amt, p_frmt_mask_cd);
2839: ELSIF p_short_name = 'PMTPRCRT' THEN
2840: hr_utility.set_location(' pm ' || p_short_name ,5382 );
2836: hr_utility.set_location(' pm ' || p_short_name ,5382 );
2837: l_rslt_elmt :=
2838: apply_format_mask(ben_ext_person.g_enrt_mntot_prem_amt, p_frmt_mask_cd);
2839: ELSIF p_short_name = 'PMTPRCRT' THEN
2840: hr_utility.set_location(' pm ' || p_short_name ,5382 );
2841: l_rslt_elmt :=
2842: apply_format_mask(ben_ext_person.g_enrt_mntot_prem_cramt, p_frmt_mask_cd);
2843: end if ;
2844: --
3169: ELSIF p_short_name = 'PRMTYP' THEN
3170: l_rslt_elmt := ben_ext_person.g_prem_type;
3171: ELSIF p_short_name = 'PRMYEAR' THEN
3172: l_rslt_elmt := ben_ext_person.g_prem_year;
3173: hr_utility.set_location(' year ' || ben_ext_person.g_prem_year , 5484);
3174: ELSIF p_short_name = 'PRMMNTH' THEN
3175: hr_utility.set_location(' month ' || ben_ext_person.g_prem_month , 119);
3176: l_rslt_elmt := ben_ext_person.g_prem_month;
3177: ELSIF p_short_name = 'PRMLUPDT' THEN
3171: ELSIF p_short_name = 'PRMYEAR' THEN
3172: l_rslt_elmt := ben_ext_person.g_prem_year;
3173: hr_utility.set_location(' year ' || ben_ext_person.g_prem_year , 5484);
3174: ELSIF p_short_name = 'PRMMNTH' THEN
3175: hr_utility.set_location(' month ' || ben_ext_person.g_prem_month , 119);
3176: l_rslt_elmt := ben_ext_person.g_prem_month;
3177: ELSIF p_short_name = 'PRMLUPDT' THEN
3178: Begin -- to_date removed in 115.149
3179: l_rslt_elmt := apply_format_mask(ben_ext_person.g_prem_last_upd_date,
3181: exception
3182: when others then
3183: l_rslt_elmt := ben_ext_person.g_prem_last_upd_date;
3184: end ;
3185: hr_utility.set_location(' updated date ' || ben_ext_person.g_prem_last_upd_date , 119);
3186: ELSIF p_short_name = 'PRAD1' THEN
3187: l_rslt_elmt := ben_ext_person.g_prim_address_line_1;
3188: ELSIF p_short_name = 'PRAD2' THEN
3189: l_rslt_elmt := ben_ext_person.g_prim_address_line_2;
3241: ELSIF p_short_name = 'PRZIP' THEN
3242: l_rslt_elmt := ben_ext_person.g_prim_postal_code;
3243: -- Participation Rate - Reimbursement
3244: ELSIF p_short_name = 'PRREIMBRS' THEN
3245: hr_utility.set_location(' PRREIMBRS '|| ben_ext_person.g_er_reimbursement,937);
3246: l_rslt_elmt :=
3247: apply_format_mask(ben_ext_person.g_er_reimbursement , p_frmt_mask_cd);
3248:
3249: end if;
3251: IF p_short_name = 'PSTSCD' THEN
3252: l_rslt_elmt := ben_ext_person.g_enrt_status_cd;
3253: elsIF p_short_name = 'PSUPFLNM' THEN
3254: l_rslt_elmt := ben_ext_person.g_sup_full_name;
3255: --hr_utility.set_location('sup name '|| l_rslt_elmt,937);
3256: elsif p_short_name = 'PSUPEMNO' THEN
3257: l_rslt_elmt := ben_ext_person.g_sup_employee_number;
3258:
3259: end if;
3353: ELSIF p_short_name = 'RUNSTAT' THEN
3354: l_rslt_elmt := ben_ext_person.g_runrslt_status;
3355: ELSIF p_short_name = 'RUNVALCHR' THEN
3356: -- run result value in characted format
3357: hr_utility.set_location (' character for run result ',98 );
3358: Begin
3359: l_rslt_elmt := apply_format_mask(ben_ext_person.g_runrslt_value,
3360: p_frmt_mask_cd);
3361: exception
3365:
3366: ELSIF p_short_name = 'RUNVALDT' THEN
3367: --l_rslt_elmt := ben_ext_person.g_runrslt_value;
3368: -- to_date raise any error then sent the value as it is
3369: hr_utility.set_location (' date for run result ',98 );
3370: Begin -- to_date removed in 115.149
3371: l_rslt_elmt := apply_format_mask(fnd_date.chardate_to_date(ben_ext_person.g_runrslt_value ),
3372: p_frmt_mask_cd);
3373: exception
3376: end ;
3377: ELSIF p_short_name = 'RUNVAL' THEN
3378: --l_rslt_elmt := ben_ext_person.g_runrslt_value;
3379: -- to_number raise any error then sent the value as it is
3380: hr_utility.set_location (' number for run result ',98 );
3381: Begin
3382: l_rslt_elmt := apply_format_mask(to_number(ben_ext_person.g_runrslt_value),
3383: p_frmt_mask_cd);
3384: exception
3388: end if;
3389: end if ;
3390: elsif p_one_char_substr = 'S' then
3391: IF p_short_name = 'SEX' THEN
3392: hr_utility.set_location('sex '||ben_ext_person.g_sex,936);
3393: l_rslt_elmt := ben_ext_person.g_sex;
3394: ELSIF p_short_name = 'STDNT' THEN
3395: l_rslt_elmt := ben_ext_person.g_student_status;
3396: ELSIF p_short_name = 'STRTDT' THEN
3432: l_rslt_elmt := apply_format_Function(l_rslt_elmt,p_frmt_mask_lookup_cd);
3433: end if;
3434: --
3435:
3436: hr_utility.set_location(' Exiting:'||l_proc, 15);
3437: return l_rslt_elmt ;
3438: end get_element_value ;
3439:
3440:
3487:
3488:
3489: Begin
3490: --
3491: hr_utility.set_location('Entering'||l_proc , 5) ;
3492:
3493: hr_utility.set_location(' Rule537:'||p_data_elmt_rl, 39);
3494: open c_rule_type(p_data_elmt_rl);
3495: fetch c_rule_type into l_rule_type;
3489: Begin
3490: --
3491: hr_utility.set_location('Entering'||l_proc , 5) ;
3492:
3493: hr_utility.set_location(' Rule537:'||p_data_elmt_rl, 39);
3494: open c_rule_type(p_data_elmt_rl);
3495: fetch c_rule_type into l_rule_type;
3496: close c_rule_type;
3497: --
3494: open c_rule_type(p_data_elmt_rl);
3495: fetch c_rule_type into l_rule_type;
3496: close c_rule_type;
3497: --
3498: hr_utility.set_location(' Rule537:'||l_rule_type.formula_type_id, 39);
3499: open c_asg;
3500: fetch c_asg into l_asg;
3501: close c_asg;
3502: --
3534: --end if;
3535: --
3536: elsif l_rule_type.formula_type_id = -531 then -- Enrollment Level rule
3537: -- Call formula initialise routine
3538: hr_utility.set_location('g_enrt_prtt_enrt_rslt_id' ||ben_ext_person.g_enrt_prtt_enrt_rslt_id, 886) ;
3539: l_outputs := benutils.formula
3540: (p_formula_id => p_data_elmt_rl,
3541: p_effective_date => ben_ext_person.g_benefits_ext_dt,
3542: p_assignment_id => l_asg.assignment_id,
3586: --
3587: l_rslt_elmt := l_outputs(l_outputs.first).value;
3588: --
3589: elsif l_rule_type.formula_type_id = -537 then -- Premium Level Rule
3590: hr_utility.set_location(' Rule537:'||l_proc, 35);
3591: -- Call formula initialise routine
3592: l_outputs := benutils.formula
3593: (p_formula_id => p_data_elmt_rl,
3594: p_effective_date => ben_ext_person.g_benefits_ext_dt,
3823: when others then
3824: null;
3825: end;
3826: --
3827: hr_utility.set_location('Exiting '||l_proc || ' with ' || l_rslt_elmt, 5) ;
3828: Return l_rslt_elmt ;
3829:
3830:
3831: end Calculate_formula;
3843: l_value number ;
3844: l_proc varchar2(72) := g_package||'Calculate_calc_value';
3845: l_err_message varchar2(2000) := 'Zero Divide Error' ;
3846: begin
3847: hr_utility.set_location('Entering'||l_proc, 5) ;
3848: hr_utility.set_location('p_firtst_value'||p_firtst_value, 5) ;
3849: hr_utility.set_location('p_second_value'||p_second_value, 5) ;
3850: hr_utility.set_location('p_calc '||p_calc , 5) ;
3851:
3844: l_proc varchar2(72) := g_package||'Calculate_calc_value';
3845: l_err_message varchar2(2000) := 'Zero Divide Error' ;
3846: begin
3847: hr_utility.set_location('Entering'||l_proc, 5) ;
3848: hr_utility.set_location('p_firtst_value'||p_firtst_value, 5) ;
3849: hr_utility.set_location('p_second_value'||p_second_value, 5) ;
3850: hr_utility.set_location('p_calc '||p_calc , 5) ;
3851:
3852: if p_calc = 'ADD' then
3845: l_err_message varchar2(2000) := 'Zero Divide Error' ;
3846: begin
3847: hr_utility.set_location('Entering'||l_proc, 5) ;
3848: hr_utility.set_location('p_firtst_value'||p_firtst_value, 5) ;
3849: hr_utility.set_location('p_second_value'||p_second_value, 5) ;
3850: hr_utility.set_location('p_calc '||p_calc , 5) ;
3851:
3852: if p_calc = 'ADD' then
3853:
3846: begin
3847: hr_utility.set_location('Entering'||l_proc, 5) ;
3848: hr_utility.set_location('p_firtst_value'||p_firtst_value, 5) ;
3849: hr_utility.set_location('p_second_value'||p_second_value, 5) ;
3850: hr_utility.set_location('p_calc '||p_calc , 5) ;
3851:
3852: if p_calc = 'ADD' then
3853:
3854: l_value := nvl(p_firtst_value,0)+ nvl(p_second_value,0) ;
3892: l_value := null ;
3893: end if ;
3894: end if ;
3895:
3896: hr_utility.set_location(' Exiting' ||l_value ||' :'||l_proc, 15);
3897: return l_value ;
3898: end Calculate_calc_value ;
3899:
3900:
3961: and a.ext_data_elmt_id = p_ext_data_elmt_id ;
3962:
3963: begin
3964:
3965: hr_utility.set_location('Entering'||l_proc, 5);
3966: for elmt in c_calc_elmt Loop
3967:
3968: open c_seq (p_ext_rcd_id , elmt.ext_data_elmt_id) ;
3969: fetch c_seq into l_seq_num ;
3968: open c_seq (p_ext_rcd_id , elmt.ext_data_elmt_id) ;
3969: fetch c_seq into l_seq_num ;
3970: close c_seq ;
3971: l_rslt_calc := null ;
3972: hr_utility.set_location (' sequence number ' || l_seq_num , 99) ;
3973:
3974: if nvl(p_seq_num,-1) > nvl(l_seq_num,0) and g_val_tab(l_seq_num) is not null then
3975: l_rslt_calc := g_val_tab(l_seq_num) ;
3976: hr_utility.set_location (' element inprev calc val ' || g_val_tab(l_seq_num) , 99) ;
3972: hr_utility.set_location (' sequence number ' || l_seq_num , 99) ;
3973:
3974: if nvl(p_seq_num,-1) > nvl(l_seq_num,0) and g_val_tab(l_seq_num) is not null then
3975: l_rslt_calc := g_val_tab(l_seq_num) ;
3976: hr_utility.set_location (' element inprev calc val ' || g_val_tab(l_seq_num) , 99) ;
3977:
3978: else
3979: if elmt.data_elmt_typ_cd in ('F', 'D') then
3980: l_rslt_calc := get_element_value(
4012:
4013: else
4014: l_rslt_calc := null ;
4015: end if ;
4016: hr_utility.set_location (' element val ' || l_rslt_calc , 99) ;
4017: end if ;
4018: end if ;
4019: --- check for the number
4020:
4026: end ;
4027:
4028: if l_rslt_elmt is null then
4029: l_rslt_elmt := l_rslt_calc ;
4030: hr_utility.set_location(' first calc value : '||l_rslt_elmt, 15);
4031:
4032: else
4033: if l_rslt_calc is not null then
4034: hr_utility.set_location(' firs and sec calc value : '||l_rslt_elmt || ' '|| l_rslt_calc , 15);
4030: hr_utility.set_location(' first calc value : '||l_rslt_elmt, 15);
4031:
4032: else
4033: if l_rslt_calc is not null then
4034: hr_utility.set_location(' firs and sec calc value : '||l_rslt_elmt || ' '|| l_rslt_calc , 15);
4035: l_rslt_elmt := Calculate_calc_value
4036: (p_firtst_value => to_number(l_rslt_elmt)
4037: ,p_second_value => l_rslt_calc
4038: ,p_calc => p_calc ) ;
4036: (p_firtst_value => to_number(l_rslt_elmt)
4037: ,p_second_value => l_rslt_calc
4038: ,p_calc => p_calc ) ;
4039:
4040: hr_utility.set_location(' return calcaulated alue : '|| l_rslt_elmt , 15);
4041:
4042: end if ;
4043: end if ;
4044:
4044:
4045: end loop ;
4046: l_rslt_elmt := apply_format_mask(to_number(l_rslt_elmt), p_frmt_mask_cd);
4047:
4048: hr_utility.set_location(' Exiting:'||l_proc, 15);
4049: return l_rslt_elmt ;
4050: end get_calc_value ;
4051:
4052:
4179: --
4180: begin
4181: --
4182:
4183: hr_utility.set_location('Entering'||l_proc, 5);
4184: --
4185: g_person_id := p_person_id;
4186:
4187: -- when subheader process the bg id is passed as parameter
4193: end if ;
4194:
4195:
4196:
4197: hr_utility.set_location('person_id '||g_person_id , 5);
4198: -- Check for required record present
4199: --
4200: /*
4201: -- Check for required record
4229: -- of a given record type (Detail) and of a given level
4230: -- (Personal, Enrollment, Dependent), if applicable.
4231: --
4232: --
4233: --hr_utility.set_location(' format max ' || ben_extract.gtt_rcd_typ_vals.last,99);
4234:
4235: FOR i IN ben_extract.gtt_rcd_typ_vals.first .. ben_extract.gtt_rcd_typ_vals.last LOOP
4236: --
4237: --
4234:
4235: FOR i IN ben_extract.gtt_rcd_typ_vals.first .. ben_extract.gtt_rcd_typ_vals.last LOOP
4236: --
4237: --
4238: --hr_utility.set_location(' low level ' ||ben_extract.gtt_rcd_typ_vals(i).low_lvl_cd,99);
4239: IF ben_extract.gtt_rcd_typ_vals(i).rcd_type_cd = p_rcd_typ_cd
4240: AND nvl(ben_extract.gtt_rcd_typ_vals(i).low_lvl_cd,' ') = nvl(p_low_lvl_cd, ' ')
4241: THEN
4242:
4255: close c_rcd_in_file_flag ;
4256: -- Initialize array
4257: --
4258:
4259: hr_utility.set_location('l_rcd_rqd_flag'|| l_rcd_rqd_flag,10);
4260: g_val_tab := g_val_def;
4261: --
4262: open rcd_elmt_c ;
4263: fetch rcd_elmt_c bulk collect into
4320:
4321: end if ;
4322:
4323: if g_debug then
4324: hr_utility.set_location('short name '||elmt.short_name,90);
4325: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4326: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4327: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4328: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4321: end if ;
4322:
4323: if g_debug then
4324: hr_utility.set_location('short name '||elmt.short_name,90);
4325: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4326: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4327: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4328: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4329: hr_utility.set_location(' name '||elmt.name,90);
4322:
4323: if g_debug then
4324: hr_utility.set_location('short name '||elmt.short_name,90);
4325: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4326: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4327: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4328: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4329: hr_utility.set_location(' name '||elmt.name,90);
4330: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4323: if g_debug then
4324: hr_utility.set_location('short name '||elmt.short_name,90);
4325: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4326: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4327: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4328: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4329: hr_utility.set_location(' name '||elmt.name,90);
4330: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4331: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4324: hr_utility.set_location('short name '||elmt.short_name,90);
4325: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4326: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4327: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4328: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4329: hr_utility.set_location(' name '||elmt.name,90);
4330: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4331: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4332: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4325: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4326: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4327: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4328: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4329: hr_utility.set_location(' name '||elmt.name,90);
4330: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4331: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4332: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4333: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4326: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4327: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4328: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4329: hr_utility.set_location(' name '||elmt.name,90);
4330: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4331: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4332: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4333: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4334: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4327: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4328: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4329: hr_utility.set_location(' name '||elmt.name,90);
4330: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4331: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4332: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4333: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4334: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4335:
4328: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4329: hr_utility.set_location(' name '||elmt.name,90);
4330: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4331: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4332: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4333: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4334: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4335:
4336:
4329: hr_utility.set_location(' name '||elmt.name,90);
4330: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4331: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4332: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4333: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4334: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4335:
4336:
4337: end if ;
4330: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4331: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4332: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4333: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4334: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4335:
4336:
4337: end if ;
4338: --
4340: --
4341: -- check field include/suppress condition
4342: --
4343: g_elmt_name := elmt.name ;
4344: hr_utility.set_location('ELEMENT '||elmt.short_name,10);
4345: hr_utility.set_location('Type Code '||elmt.data_elmt_typ_cd,10);
4346:
4347: IF elmt.data_elmt_typ_cd in ('F','D') THEN
4348: --
4341: -- check field include/suppress condition
4342: --
4343: g_elmt_name := elmt.name ;
4344: hr_utility.set_location('ELEMENT '||elmt.short_name,10);
4345: hr_utility.set_location('Type Code '||elmt.data_elmt_typ_cd,10);
4346:
4347: IF elmt.data_elmt_typ_cd in ('F','D') THEN
4348: --
4349: -- data element is a field:
4365: end if ;
4366:
4367: --- decide the grouping element subhead
4368: if ben_ext_thread.g_ext_group_elmt1 is not null then
4369: hr_utility.set_location(' GROUP ' || ben_ext_thread.g_ext_group_elmt1 || ' / ' ||elmt.short_name , 99 ) ;
4370: if ben_ext_thread.g_ext_group_elmt1 = elmt.short_name then
4371: ben_ext_person.g_group_elmt_value1 := l_rslt_elmt ;
4372: end if ;
4373: if ben_ext_thread.g_ext_group_elmt2 is not null then
4380: else
4381: ben_ext_person.g_group_elmt_value2 := null ;
4382: end if ;
4383: end if ;
4384: hr_utility.set_location(' GROUP value ' || ben_ext_person.g_group_elmt_value1 , 99 ) ;
4385: else
4386: if p_rcd_typ_cd = 'S' then
4387: ben_ext_person.g_group_elmt_value1 := ' ' ;
4388: ben_ext_person.g_group_elmt_value2 := ' ' ;
4456: --- the max_string , the function down use the max lengh
4457: --- use the min of lenght and max lenght so format here only
4458: if elmt.max_length_num is not null then
4459: l_rslt_elmt := rpad(nvl(l_rslt_elmt,' '),elmt.max_length_num);
4460: hr_utility.set_location(' string max lenght '|| l_rslt_elmt,100 );
4461: end if ;
4462:
4463: ELSIF elmt.data_elmt_typ_cd = 'P' THEN
4464: -- payroll balance
4461: end if ;
4462:
4463: ELSIF elmt.data_elmt_typ_cd = 'P' THEN
4464: -- payroll balance
4465: hr_utility.set_location(' payroll balance id '|| elmt.defined_balance_id ,100 );
4466: -- l_rslt_elmt := '00.00';
4467: -- format mask handlinga
4468: if ben_Ext_person.g_assignment_id is not null then
4469: l_rslt_elmt := get_pay_balance(
4486: when others then
4487: null;
4488: end;
4489: end if ;
4490: hr_utility.set_location(' payroll balance '|| l_rslt_elmt,100 );
4491:
4492: END IF;
4493: --
4494: -- if resulting data element (field) is null, substitute it with
4512: l_rslt_elmt := SUBSTR(l_rslt_elmt, -l_max_len);
4513: else -- everything else truncs from the right.
4514: l_rslt_elmt := SUBSTR(l_rslt_elmt, 1, elmt.max_length_num);
4515: end if;
4516: hr_utility.set_location(' after max lenght '|| l_rslt_elmt,100 );
4517: --
4518: END IF;
4519: --
4520: --
4597: and ben_extract.gtt_rcd_rqd_vals_seq(rci).seq_num = ben_extract.gtt_rcd_typ_vals(i).seq_num
4598: )
4599: OR ben_extract.gtt_rcd_rqd_vals_seq(rci).low_lvl_cd = 'NOREQDRCD'
4600: THEN
4601: hr_utility.set_location('mandatory found' || p_low_lvl_cd , 99 );
4602: hr_utility.set_location('mandatory found' || ben_extract.gtt_rcd_typ_vals(i).seq_num , 99 );
4603: ben_extract.gtt_rcd_rqd_vals_seq(rci).rcd_found := TRUE;
4604: END IF;
4605: END LOOP ;
4598: )
4599: OR ben_extract.gtt_rcd_rqd_vals_seq(rci).low_lvl_cd = 'NOREQDRCD'
4600: THEN
4601: hr_utility.set_location('mandatory found' || p_low_lvl_cd , 99 );
4602: hr_utility.set_location('mandatory found' || ben_extract.gtt_rcd_typ_vals(i).seq_num , 99 );
4603: ben_extract.gtt_rcd_rqd_vals_seq(rci).rcd_found := TRUE;
4604: END IF;
4605: END LOOP ;
4606:
4646: --l_scnd_sort_val := ' ' ;
4647: end if ;
4648:
4649: --
4650: hr_utility.set_location( ' l_chg_rcd_upd_flag ' || p_data_typ_cd || ' / ' || l_chg_rcd_upd_flag, 99 ) ;
4651: l_ext_chg_rcd_mode := 'C' ;
4652: if p_data_typ_cd = 'C' and l_chg_rcd_upd_flag = 'Y' then
4653: -- of the record to be sonsoldated for change event extract
4654:
4961: ,p_object_version_number => l_object_version_number
4962: ,p_ext_chg_rcd_mode => l_ext_chg_rcd_mode
4963: ,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
4964: );
4965: hr_utility.set_location(' Mode ' || l_ext_chg_rcd_mode,99 ) ;
4966: end if ;
4967:
4968: -- if change extract and update flag defined and mode is update then
4969: if p_data_typ_cd = 'C' and l_chg_rcd_upd_flag = 'Y' and l_ext_chg_rcd_mode = 'U' then
5614: --
5615: --
5616: end if; --write record = 'Y'
5617: /* madatory validated down Else
5618: hr_utility.set_location(' Element not found ', 15);
5619: --- If the element is mandatory and null and the record is mandatory
5620: --- skip the person
5621: if nvl(l_rcd_rqd_flag,'N') = 'Y' then
5622: hr_utility.set_location(' record not found ', 15) ;
5618: hr_utility.set_location(' Element not found ', 15);
5619: --- If the element is mandatory and null and the record is mandatory
5620: --- skip the person
5621: if nvl(l_rcd_rqd_flag,'N') = 'Y' then
5622: hr_utility.set_location(' record not found ', 15) ;
5623: raise ben_ext_person.detail_error ;
5624: end if ;
5625: */
5626: end if ;
5630: --
5631: END LOOP;
5632:
5633: --
5634: hr_utility.set_location(' Exiting:'||l_proc, 15);
5635: --
5636: end process_ext_recs;
5637: --
5638: -- The following three functions are overloaded.
5651:
5652: --
5653: Begin
5654: --
5655: hr_utility.set_location('Entering:'||l_proc, 5);
5656: --
5657: if p_value is null then
5658: return p_value;
5659: end if;
5670: end if;
5671: --
5672: return (l_fmt_date);
5673: --
5674: hr_utility.set_location(' Exiting:'||l_proc, 15);
5675: --
5676: End apply_format_mask;
5677: --
5678: -- ----------------------------------------------------------------------------
5688: l_format varchar2(100);
5689: --
5690: Begin
5691: --
5692: hr_utility.set_location('Entering:'||l_proc, 10);
5693: --
5694: if p_value is null then
5695: return p_value;
5696: end if;
5696: end if;
5697: --
5698: if p_format_mask is not null then
5699: --
5700: hr_utility.set_location('format :'||p_format_mask, 10);
5701: hr_utility.set_location('value :'||p_value , 10);
5702: -- Check for special format
5703: --
5704: if substr(p_format_mask,length(p_format_mask),1) in ('{','}') then
5697: --
5698: if p_format_mask is not null then
5699: --
5700: hr_utility.set_location('format :'||p_format_mask, 10);
5701: hr_utility.set_location('value :'||p_value , 10);
5702: -- Check for special format
5703: --
5704: if substr(p_format_mask,length(p_format_mask),1) in ('{','}') then
5705: -- check the first char of the code
5711: if l_cd_strt = 'N' then
5712:
5713: --- convert the number as per the format
5714: l_format := substr(p_format_mask,1, length(p_format_mask)-1) ;
5715: hr_utility.set_location('l_format :'||l_format , 10);
5716:
5717: l_str_value := to_char(p_value,l_format);
5718:
5719: hr_utility.set_location('Formated number :'||l_str_value , 10);
5715: hr_utility.set_location('l_format :'||l_format , 10);
5716:
5717: l_str_value := to_char(p_value,l_format);
5718:
5719: hr_utility.set_location('Formated number :'||l_str_value , 10);
5720:
5721: -- as per the bug 5387355 , The Last digit of the number has to be changed
5722: -- if the number is + then 1 to 9 changes as A to I and 0 chages as {
5723: -- if the number is - then 1 to 9 changes as J to R and 0 chages as }
5753: '1','J','2','K','3','L','4','M','5','N','6','O','7','P','8','Q','9','R','0','}')
5754: into l_fmt_num from dual;
5755: end if;
5756:
5757: hr_utility.set_location('non Formated value :'||l_str_value , 10);
5758: hr_utility.set_location('non Formated value :'|| (length(l_str_value)-1) , 10);
5759:
5760: l_fmt_num := substr(l_str_value ,1, (length(l_str_value)-1) )||l_fmt_num;
5761:
5754: into l_fmt_num from dual;
5755: end if;
5756:
5757: hr_utility.set_location('non Formated value :'||l_str_value , 10);
5758: hr_utility.set_location('non Formated value :'|| (length(l_str_value)-1) , 10);
5759:
5760: l_fmt_num := substr(l_str_value ,1, (length(l_str_value)-1) )||l_fmt_num;
5761:
5762: hr_utility.set_location('Formated value :'||l_fmt_num , 10);
5758: hr_utility.set_location('non Formated value :'|| (length(l_str_value)-1) , 10);
5759:
5760: l_fmt_num := substr(l_str_value ,1, (length(l_str_value)-1) )||l_fmt_num;
5761:
5762: hr_utility.set_location('Formated value :'||l_fmt_num , 10);
5763:
5764: end if;
5765: else --- for {} formats
5766: --
5775: end if;
5776: --
5777: return (rtrim(ltrim(l_fmt_num)));
5778: --
5779: hr_utility.set_location(' Exiting:'||l_proc, 10);
5780: --
5781: End apply_format_mask;
5782: --
5783: -- ----------------------------------------------------------------------------
5793: l_err_message varchar2(2000) ;
5794: --
5795: Begin
5796: --
5797: hr_utility.set_location('Entering:'||l_proc, 15);
5798: hr_utility.set_location('value :'||p_value||' - ' || p_format_mask, 15);
5799: --
5800: if p_value is null then
5801: return p_value;
5794: --
5795: Begin
5796: --
5797: hr_utility.set_location('Entering:'||l_proc, 15);
5798: hr_utility.set_location('value :'||p_value||' - ' || p_format_mask, 15);
5799: --
5800: if p_value is null then
5801: return p_value;
5802: end if;
5805: l_err_message := get_error_msg(92065,'BEN_92065_EXT_FRMT_INVALID',g_elmt_name );
5806: --
5807: if p_format_mask is not null then
5808: --
5809: hr_utility.set_location('format mask :' || p_format_mask, 16);
5810: l_value_stripped := translate(p_value,
5811: '1234567890 !@#$%^&*()-_+={}|:"<>?,./;''[]\`~',
5812: '1234567890');
5813: --
5983: else
5984: --
5985: l_fmt_string := p_value;
5986: --
5987: hr_utility.set_location('value :' || l_fmt_string, 16);
5988: end if;
5989: --
5990: return (rtrim(ltrim(l_fmt_string)));
5991: --
5988: end if;
5989: --
5990: return (rtrim(ltrim(l_fmt_string)));
5991: --
5992: hr_utility.set_location(' Exiting:'||l_proc, 15);
5993: --
5994: End apply_format_mask;
5995:
5996: -- ------------------------------------------------------------------
6005: l_err_message varchar2(2000) ;
6006: --
6007: Begin
6008: --
6009: hr_utility.set_location('Entering:'||l_proc, 15);
6010: hr_utility.set_location('value :'||p_value||' - ' || p_format_mask, 15);
6011: --
6012: if p_value is null then
6013: return p_value;
6006: --
6007: Begin
6008: --
6009: hr_utility.set_location('Entering:'||l_proc, 15);
6010: hr_utility.set_location('value :'||p_value||' - ' || p_format_mask, 15);
6011: --
6012: if p_value is null then
6013: return p_value;
6014: end if;
6044: );
6045: --
6046: end if;
6047: end if ;
6048: hr_utility.set_location(' Exiting:'||l_proc, 15);
6049: return l_fmt_string ;
6050: end apply_format_function ;
6051:
6052:
6083: ;
6084: --
6085: Begin
6086: --
6087: hr_utility.set_location('Entering:'||l_proc, 5);
6088: --
6089: if p_ext_rcd_in_file_id is not null then
6090: --
6091: open incl_rcd_c;
6107: end if;
6108: --
6109: return (l_sprs_or_incl);
6110: --
6111: hr_utility.set_location(' Exiting:'||l_proc, 15);
6112: --
6113: End sprs_or_incl;
6114: --
6115: --