[Home] [Help]
5: -- ----------------------------------------------------------------------------
6: -- | Private Global Definitions |
7: -- ----------------------------------------------------------------------------
8: --
9: g_debug boolean := hr_utility.debug_enabled;
10: g_package varchar2(33) := ' ben_ext_fmt.'; -- Global package name
11: g_val_def valtabtyp := valtabtyp(null,null,null,null,null,null,null,null,null,null,
12: null,null,null,null,null,null,null,null,null,null,
13: null,null,null,null,null,null,null,null,null,null,
72: l_rslt_elmt varchar2(300) ;
73:
74: begin
75:
76: hr_utility.set_location('Entering:'||l_proc, 5);
77:
78:
79: open c_leg ;
80: fetch c_leg into l_LEGISLATION_CODE ;
88: ,p_legislation_code => l_LEGISLATION_CODE
89: ,p_defined_balance_id => p_defined_balance_id
90: ) ;
91:
92: hr_utility.set_location(' Exiting: ' || l_rslt_elmt || ' : ' ||l_proc, 15);
93: return l_rslt_elmt ;
94: end ;
95:
96:
105: val_name varchar2(250);
106: --
107: Begin
108: --
109: hr_utility.set_location('Entering:'||l_proc, 5);
110: --
111: select user_name into val_name
112: from fnd_user
113: where user_id = p_val_id;
111: select user_name into val_name
112: from fnd_user
113: where user_id = p_val_id;
114:
115: hr_utility.set_location(' Exiting:'||l_proc, 15);
116: return val_name;
117: --
118: --
119: Exception
118: --
119: Exception
120: When NO_DATA_FOUND Then
121: --
122: hr_utility.set_location(' Exiting:'||l_proc, 999);
123: --
124: return null;
125: End get_name;
126: --
137: l_err_message fnd_new_messages.message_text%type ;
138: l_proc varchar2(72) := g_package||'get_error_msg';
139: begin
140:
141: hr_utility.set_location('Entering'||l_proc, 5);
142: hr_utility.set_location('Error'||substr(p_err_name,1,100), 99.99);
143: fnd_message.set_name('BEN',p_err_name);
144: if P_err_no in ( 91888,92065,91924,91887,92313,92312,92678,92679 )
145: and p_token1 is not null then
138: l_proc varchar2(72) := g_package||'get_error_msg';
139: begin
140:
141: hr_utility.set_location('Entering'||l_proc, 5);
142: hr_utility.set_location('Error'||substr(p_err_name,1,100), 99.99);
143: fnd_message.set_name('BEN',p_err_name);
144: if P_err_no in ( 91888,92065,91924,91887,92313,92312,92678,92679 )
145: and p_token1 is not null then
146: fnd_message.set_token('DATA' , p_token1 ) ;
150: -- if any new error calls this then the totken is to be added here
151: -- fnd_message.set_token('FIELD','p_prvs_stat_cd');
152: -- fnd_message.set_token('TYPE','BEN_PER_IN_LER_STAT_CD');
153: l_err_message := fnd_message.get ;
154: hr_utility.set_location('message'||substr(l_err_message,1,100), 99.99);
155: hr_utility.set_location('Exiting'||l_proc, 5);
156: return (l_err_message) ;
157:
158: end ;
151: -- fnd_message.set_token('FIELD','p_prvs_stat_cd');
152: -- fnd_message.set_token('TYPE','BEN_PER_IN_LER_STAT_CD');
153: l_err_message := fnd_message.get ;
154: hr_utility.set_location('message'||substr(l_err_message,1,100), 99.99);
155: hr_utility.set_location('Exiting'||l_proc, 5);
156: return (l_err_message) ;
157:
158: end ;
159:
172: --
173: begin
174: --
175: --
176: hr_utility.set_location('Entering'||l_proc, 5);
177: --
178: if p_ext_data_elmt_typ = 'F' then
179: l_val := p_name;
180: else
181: l_val := p_id;
182: end if;
183: return(l_val);
184: --
185: hr_utility.set_location('Exiting:'||l_proc, 15);
186: --
187: end decode_setup;
188: --
189:
215: and val = nvl(p_value, ' ');
216: --
217: Begin
218: --
219: hr_utility.set_location('Entering:'||l_proc, 5);
220: hr_utility.set_location('code for decode ' ||p_value||' '|| p_ext_data_elmt_id ,936);
221: --
222: hr_utility.set_location('MONSTER defa '||p_default ,10);
223:
216: --
217: Begin
218: --
219: hr_utility.set_location('Entering:'||l_proc, 5);
220: hr_utility.set_location('code for decode ' ||p_value||' '|| p_ext_data_elmt_id ,936);
221: --
222: hr_utility.set_location('MONSTER defa '||p_default ,10);
223:
224: if p_value is not null then
218: --
219: hr_utility.set_location('Entering:'||l_proc, 5);
220: hr_utility.set_location('code for decode ' ||p_value||' '|| p_ext_data_elmt_id ,936);
221: --
222: hr_utility.set_location('MONSTER defa '||p_default ,10);
223:
224: if p_value is not null then
225: --
226: open c1;
287: else
288: --
289: if p_default is not null and upper(p_default) <> 'NULL' then
290: --
291: hr_utility.set_location(' retunr defaul :'||p_default, 15);
292: return(p_default);
293: --
294: else
295: --
298: end if;
299: --
300: end if;
301: --
302: hr_utility.set_location(' Exiting:'||l_proc, 15);
303: --
304: End apply_decode;
305:
306: --
331: --
332: l_rslt_elmt varchar2(4000) ;
333:
334: begin
335: hr_utility.set_location('Entering'||l_proc, 5);
336:
337: if p_one_char_substr = 'A' then
338: --
339: if p_two_char_substr in ('AC','AP') then
810:
811: END IF;
812: elsif p_two_char_substr = 'CW' then
813: --- CWB Elements
814: hr_utility.set_location(' cwb extract ' || p_two_char_substr || ' /' || p_short_name , 99 );
815: IF p_short_name = 'CWBLEN' then
816: l_rslt_elmt := ben_ext_person.g_CWB_Life_Event_Name ;
817: ELSIF p_short_name = 'CWBLEOD' then
818: l_rslt_elmt := apply_format_mask (ben_ext_person.g_CWB_Life_Event_Occurred_Date,p_frmt_mask_cd);
953: ELSIF p_short_name = 'CWBPF' then
954: l_rslt_elmt := ben_ext_person.g_CWB_Person_FREQUENCY ;
955: ELSIF p_short_name = 'CWBPFN' then
956: l_rslt_elmt := ben_ext_person.g_CWB_Person_FULL_NAME ;
957: hr_utility.set_location(' cwbfn ' || ben_ext_person.g_CWB_Person_FULL_NAME , 99 );
958: ELSIF p_short_name = 'CWBPGAF' then
959: l_rslt_elmt := ben_ext_person.g_CWB_Person_GRADE_ANN_FACTOR ;
960: ELSIF p_short_name = 'CWBPGC' then
961: l_rslt_elmt := ben_ext_person.g_CWB_Person_Grade_COMPARATIO ;
1589: l_rslt_elmt :=
1590: apply_format_mask(ben_ext_person.g_element_entry_eff_start_date,p_frmt_mask_cd);
1591: ELSIF p_short_name = 'ELVESDT' THEN
1592: l_rslt_elmt := apply_format_mask(ben_ext_person.g_element_eev_eff_strt_date,p_frmt_mask_cd);
1593: hr_utility.set_location('eeveffdate '||ben_ext_person.g_element_eev_eff_strt_date ,99);
1594:
1595: ELSIF p_short_name = 'ELVEEDT' THEN
1596: if ben_ext_person.g_element_eev_eff_end_date = hr_api.g_eot then
1597: l_rslt_elmt := null ;
1597: l_rslt_elmt := null ;
1598: else
1599: l_rslt_elmt := apply_format_mask(ben_ext_person.g_element_eev_eff_end_date,p_frmt_mask_cd);
1600: end if ;
1601: hr_utility.set_location('eeveffdate '||ben_ext_person.g_element_eev_eff_end_date ,99);
1602: ELSIF p_short_name = 'ELESTS' THEN
1603: l_rslt_elmt := ben_ext_person.g_elig_lfevt_status;
1604: ELSIF p_short_name = 'ELEVL' THEN
1605: -- checking whether numeric formating applicable
1735: to_char(ben_ext_person.g_employee_status_id));
1736: ELSIF p_short_name = 'EMPASID' then
1737: l_rslt_elmt := ben_ext_person.g_assignment_id ;
1738: ELSIF p_short_name = 'EMPASTY' then
1739: hr_utility.set_location(' EMPASTY ' || ben_ext_person.g_asg_type, 99 );
1740: l_rslt_elmt := ben_ext_person.g_asg_type;
1741: end if;
1742: elsif p_two_char_substr = 'EN' then
1743: IF p_short_name = 'ENDDT' THEN
2588: l_rslt_elmt :=
2589: apply_format_mask(ben_ext_person.g_er_cwb_worksheet_bdgt , p_frmt_mask_cd);
2590: --Enrollment Rate - CWB Worksheet Amount
2591: ELSIF p_short_name = 'PECWBWSAM' THEN
2592: hr_utility.set_location(' Worksheet Amount ' || ben_ext_person.g_er_cwb_worksheet_amt,991);
2593: l_rslt_elmt :=
2594: apply_format_mask(ben_ext_person.g_er_cwb_worksheet_amt , p_frmt_mask_cd);
2595: --Enrollment Rate - CWB Misc Rate 3
2596: ELSIF p_short_name = 'PECWBMSR3' THEN
2829: l_rslt_elmt := ben_ext_person.g_ptp_attr_10;
2830: end if;
2831: elsif p_two_char_substr = 'PM' then
2832: IF p_short_name = 'PMTPRAMT' THEN
2833: hr_utility.set_location(' pm ' || p_short_name ,5382 );
2834: l_rslt_elmt :=
2835: apply_format_mask(ben_ext_person.g_enrt_mntot_prem_amt, p_frmt_mask_cd);
2836: ELSIF p_short_name = 'PMTPRCRT' THEN
2837: hr_utility.set_location(' pm ' || p_short_name ,5382 );
2833: hr_utility.set_location(' pm ' || p_short_name ,5382 );
2834: l_rslt_elmt :=
2835: apply_format_mask(ben_ext_person.g_enrt_mntot_prem_amt, p_frmt_mask_cd);
2836: ELSIF p_short_name = 'PMTPRCRT' THEN
2837: hr_utility.set_location(' pm ' || p_short_name ,5382 );
2838: l_rslt_elmt :=
2839: apply_format_mask(ben_ext_person.g_enrt_mntot_prem_cramt, p_frmt_mask_cd);
2840: end if ;
2841: --
3163: ELSIF p_short_name = 'PRMTYP' THEN
3164: l_rslt_elmt := ben_ext_person.g_prem_type;
3165: ELSIF p_short_name = 'PRMYEAR' THEN
3166: l_rslt_elmt := ben_ext_person.g_prem_year;
3167: hr_utility.set_location(' year ' || ben_ext_person.g_prem_year , 5484);
3168: ELSIF p_short_name = 'PRMMNTH' THEN
3169: hr_utility.set_location(' month ' || ben_ext_person.g_prem_month , 119);
3170: l_rslt_elmt := ben_ext_person.g_prem_month;
3171: ELSIF p_short_name = 'PRMLUPDT' THEN
3165: ELSIF p_short_name = 'PRMYEAR' THEN
3166: l_rslt_elmt := ben_ext_person.g_prem_year;
3167: hr_utility.set_location(' year ' || ben_ext_person.g_prem_year , 5484);
3168: ELSIF p_short_name = 'PRMMNTH' THEN
3169: hr_utility.set_location(' month ' || ben_ext_person.g_prem_month , 119);
3170: l_rslt_elmt := ben_ext_person.g_prem_month;
3171: ELSIF p_short_name = 'PRMLUPDT' THEN
3172: Begin -- to_date removed in 115.149
3173: l_rslt_elmt := apply_format_mask(ben_ext_person.g_prem_last_upd_date,
3175: exception
3176: when others then
3177: l_rslt_elmt := ben_ext_person.g_prem_last_upd_date;
3178: end ;
3179: hr_utility.set_location(' updated date ' || ben_ext_person.g_prem_last_upd_date , 119);
3180: ELSIF p_short_name = 'PRAD1' THEN
3181: l_rslt_elmt := ben_ext_person.g_prim_address_line_1;
3182: ELSIF p_short_name = 'PRAD2' THEN
3183: l_rslt_elmt := ben_ext_person.g_prim_address_line_2;
3235: ELSIF p_short_name = 'PRZIP' THEN
3236: l_rslt_elmt := ben_ext_person.g_prim_postal_code;
3237: -- Participation Rate - Reimbursement
3238: ELSIF p_short_name = 'PRREIMBRS' THEN
3239: hr_utility.set_location(' PRREIMBRS '|| ben_ext_person.g_er_reimbursement,937);
3240: l_rslt_elmt :=
3241: apply_format_mask(ben_ext_person.g_er_reimbursement , p_frmt_mask_cd);
3242:
3243: end if;
3245: IF p_short_name = 'PSTSCD' THEN
3246: l_rslt_elmt := ben_ext_person.g_enrt_status_cd;
3247: elsIF p_short_name = 'PSUPFLNM' THEN
3248: l_rslt_elmt := ben_ext_person.g_sup_full_name;
3249: --hr_utility.set_location('sup name '|| l_rslt_elmt,937);
3250: elsif p_short_name = 'PSUPEMNO' THEN
3251: l_rslt_elmt := ben_ext_person.g_sup_employee_number;
3252:
3253: end if;
3347: ELSIF p_short_name = 'RUNSTAT' THEN
3348: l_rslt_elmt := ben_ext_person.g_runrslt_status;
3349: ELSIF p_short_name = 'RUNVALCHR' THEN
3350: -- run result value in characted format
3351: hr_utility.set_location (' character for run result ',98 );
3352: Begin
3353: l_rslt_elmt := apply_format_mask(ben_ext_person.g_runrslt_value,
3354: p_frmt_mask_cd);
3355: exception
3359:
3360: ELSIF p_short_name = 'RUNVALDT' THEN
3361: --l_rslt_elmt := ben_ext_person.g_runrslt_value;
3362: -- to_date raise any error then sent the value as it is
3363: hr_utility.set_location (' date for run result ',98 );
3364: Begin -- to_date removed in 115.149
3365: l_rslt_elmt := apply_format_mask(fnd_date.chardate_to_date(ben_ext_person.g_runrslt_value ),
3366: p_frmt_mask_cd);
3367: exception
3370: end ;
3371: ELSIF p_short_name = 'RUNVAL' THEN
3372: --l_rslt_elmt := ben_ext_person.g_runrslt_value;
3373: -- to_number raise any error then sent the value as it is
3374: hr_utility.set_location (' number for run result ',98 );
3375: Begin
3376: l_rslt_elmt := apply_format_mask(to_number(ben_ext_person.g_runrslt_value),
3377: p_frmt_mask_cd);
3378: exception
3382: end if;
3383: end if ;
3384: elsif p_one_char_substr = 'S' then
3385: IF p_short_name = 'SEX' THEN
3386: hr_utility.set_location('sex '||ben_ext_person.g_sex,936);
3387: l_rslt_elmt := ben_ext_person.g_sex;
3388: ELSIF p_short_name = 'STDNT' THEN
3389: l_rslt_elmt := ben_ext_person.g_student_status;
3390: ELSIF p_short_name = 'STRTDT' THEN
3426: l_rslt_elmt := apply_format_Function(l_rslt_elmt,p_frmt_mask_lookup_cd);
3427: end if;
3428: --
3429:
3430: hr_utility.set_location(' Exiting:'||l_proc, 15);
3431: return l_rslt_elmt ;
3432: end get_element_value ;
3433:
3434:
3481:
3482:
3483: Begin
3484: --
3485: hr_utility.set_location('Entering'||l_proc , 5) ;
3486:
3487: hr_utility.set_location(' Rule537:'||p_data_elmt_rl, 39);
3488: open c_rule_type(p_data_elmt_rl);
3489: fetch c_rule_type into l_rule_type;
3483: Begin
3484: --
3485: hr_utility.set_location('Entering'||l_proc , 5) ;
3486:
3487: hr_utility.set_location(' Rule537:'||p_data_elmt_rl, 39);
3488: open c_rule_type(p_data_elmt_rl);
3489: fetch c_rule_type into l_rule_type;
3490: close c_rule_type;
3491: --
3488: open c_rule_type(p_data_elmt_rl);
3489: fetch c_rule_type into l_rule_type;
3490: close c_rule_type;
3491: --
3492: hr_utility.set_location(' Rule537:'||l_rule_type.formula_type_id, 39);
3493: open c_asg;
3494: fetch c_asg into l_asg;
3495: close c_asg;
3496: --
3528: --end if;
3529: --
3530: elsif l_rule_type.formula_type_id = -531 then -- Enrollment Level rule
3531: -- Call formula initialise routine
3532: hr_utility.set_location('g_enrt_prtt_enrt_rslt_id' ||ben_ext_person.g_enrt_prtt_enrt_rslt_id, 886) ;
3533: l_outputs := benutils.formula
3534: (p_formula_id => p_data_elmt_rl,
3535: p_effective_date => ben_ext_person.g_benefits_ext_dt,
3536: p_assignment_id => l_asg.assignment_id,
3580: --
3581: l_rslt_elmt := l_outputs(l_outputs.first).value;
3582: --
3583: elsif l_rule_type.formula_type_id = -537 then -- Premium Level Rule
3584: hr_utility.set_location(' Rule537:'||l_proc, 35);
3585: -- Call formula initialise routine
3586: l_outputs := benutils.formula
3587: (p_formula_id => p_data_elmt_rl,
3588: p_effective_date => ben_ext_person.g_benefits_ext_dt,
3817: when others then
3818: null;
3819: end;
3820: --
3821: hr_utility.set_location('Exiting '||l_proc || ' with ' || l_rslt_elmt, 5) ;
3822: Return l_rslt_elmt ;
3823:
3824:
3825: end Calculate_formula;
3837: l_value number ;
3838: l_proc varchar2(72) := g_package||'Calculate_calc_value';
3839: l_err_message varchar2(2000) := 'Zero Divide Error' ;
3840: begin
3841: hr_utility.set_location('Entering'||l_proc, 5) ;
3842: hr_utility.set_location('p_firtst_value'||p_firtst_value, 5) ;
3843: hr_utility.set_location('p_second_value'||p_second_value, 5) ;
3844: hr_utility.set_location('p_calc '||p_calc , 5) ;
3845:
3838: l_proc varchar2(72) := g_package||'Calculate_calc_value';
3839: l_err_message varchar2(2000) := 'Zero Divide Error' ;
3840: begin
3841: hr_utility.set_location('Entering'||l_proc, 5) ;
3842: hr_utility.set_location('p_firtst_value'||p_firtst_value, 5) ;
3843: hr_utility.set_location('p_second_value'||p_second_value, 5) ;
3844: hr_utility.set_location('p_calc '||p_calc , 5) ;
3845:
3846: if p_calc = 'ADD' then
3839: l_err_message varchar2(2000) := 'Zero Divide Error' ;
3840: begin
3841: hr_utility.set_location('Entering'||l_proc, 5) ;
3842: hr_utility.set_location('p_firtst_value'||p_firtst_value, 5) ;
3843: hr_utility.set_location('p_second_value'||p_second_value, 5) ;
3844: hr_utility.set_location('p_calc '||p_calc , 5) ;
3845:
3846: if p_calc = 'ADD' then
3847:
3840: begin
3841: hr_utility.set_location('Entering'||l_proc, 5) ;
3842: hr_utility.set_location('p_firtst_value'||p_firtst_value, 5) ;
3843: hr_utility.set_location('p_second_value'||p_second_value, 5) ;
3844: hr_utility.set_location('p_calc '||p_calc , 5) ;
3845:
3846: if p_calc = 'ADD' then
3847:
3848: l_value := nvl(p_firtst_value,0)+ nvl(p_second_value,0) ;
3886: l_value := null ;
3887: end if ;
3888: end if ;
3889:
3890: hr_utility.set_location(' Exiting' ||l_value ||' :'||l_proc, 15);
3891: return l_value ;
3892: end Calculate_calc_value ;
3893:
3894:
3955: and a.ext_data_elmt_id = p_ext_data_elmt_id ;
3956:
3957: begin
3958:
3959: hr_utility.set_location('Entering'||l_proc, 5);
3960: for elmt in c_calc_elmt Loop
3961:
3962: open c_seq (p_ext_rcd_id , elmt.ext_data_elmt_id) ;
3963: fetch c_seq into l_seq_num ;
3962: open c_seq (p_ext_rcd_id , elmt.ext_data_elmt_id) ;
3963: fetch c_seq into l_seq_num ;
3964: close c_seq ;
3965: l_rslt_calc := null ;
3966: hr_utility.set_location (' sequence number ' || l_seq_num , 99) ;
3967:
3968: if nvl(p_seq_num,-1) > nvl(l_seq_num,0) and g_val_tab(l_seq_num) is not null then
3969: l_rslt_calc := g_val_tab(l_seq_num) ;
3970: hr_utility.set_location (' element inprev calc val ' || g_val_tab(l_seq_num) , 99) ;
3966: hr_utility.set_location (' sequence number ' || l_seq_num , 99) ;
3967:
3968: if nvl(p_seq_num,-1) > nvl(l_seq_num,0) and g_val_tab(l_seq_num) is not null then
3969: l_rslt_calc := g_val_tab(l_seq_num) ;
3970: hr_utility.set_location (' element inprev calc val ' || g_val_tab(l_seq_num) , 99) ;
3971:
3972: else
3973: if elmt.data_elmt_typ_cd in ('F', 'D') then
3974: l_rslt_calc := get_element_value(
4006:
4007: else
4008: l_rslt_calc := null ;
4009: end if ;
4010: hr_utility.set_location (' element val ' || l_rslt_calc , 99) ;
4011: end if ;
4012: end if ;
4013: --- check for the number
4014:
4020: end ;
4021:
4022: if l_rslt_elmt is null then
4023: l_rslt_elmt := l_rslt_calc ;
4024: hr_utility.set_location(' first calc value : '||l_rslt_elmt, 15);
4025:
4026: else
4027: if l_rslt_calc is not null then
4028: hr_utility.set_location(' firs and sec calc value : '||l_rslt_elmt || ' '|| l_rslt_calc , 15);
4024: hr_utility.set_location(' first calc value : '||l_rslt_elmt, 15);
4025:
4026: else
4027: if l_rslt_calc is not null then
4028: hr_utility.set_location(' firs and sec calc value : '||l_rslt_elmt || ' '|| l_rslt_calc , 15);
4029: l_rslt_elmt := Calculate_calc_value
4030: (p_firtst_value => to_number(l_rslt_elmt)
4031: ,p_second_value => l_rslt_calc
4032: ,p_calc => p_calc ) ;
4030: (p_firtst_value => to_number(l_rslt_elmt)
4031: ,p_second_value => l_rslt_calc
4032: ,p_calc => p_calc ) ;
4033:
4034: hr_utility.set_location(' return calcaulated alue : '|| l_rslt_elmt , 15);
4035:
4036: end if ;
4037: end if ;
4038:
4038:
4039: end loop ;
4040: l_rslt_elmt := apply_format_mask(to_number(l_rslt_elmt), p_frmt_mask_cd);
4041:
4042: hr_utility.set_location(' Exiting:'||l_proc, 15);
4043: return l_rslt_elmt ;
4044: end get_calc_value ;
4045:
4046:
4173: --
4174: begin
4175: --
4176:
4177: hr_utility.set_location('Entering'||l_proc, 5);
4178: --
4179: g_person_id := p_person_id;
4180:
4181: -- when subheader process the bg id is passed as parameter
4187: end if ;
4188:
4189:
4190:
4191: hr_utility.set_location('person_id '||g_person_id , 5);
4192: -- Check for required record present
4193: --
4194: /*
4195: -- Check for required record
4223: -- of a given record type (Detail) and of a given level
4224: -- (Personal, Enrollment, Dependent), if applicable.
4225: --
4226: --
4227: --hr_utility.set_location(' format max ' || ben_extract.gtt_rcd_typ_vals.last,99);
4228:
4229: FOR i IN ben_extract.gtt_rcd_typ_vals.first .. ben_extract.gtt_rcd_typ_vals.last LOOP
4230: --
4231: --
4228:
4229: FOR i IN ben_extract.gtt_rcd_typ_vals.first .. ben_extract.gtt_rcd_typ_vals.last LOOP
4230: --
4231: --
4232: --hr_utility.set_location(' low level ' ||ben_extract.gtt_rcd_typ_vals(i).low_lvl_cd,99);
4233: IF ben_extract.gtt_rcd_typ_vals(i).rcd_type_cd = p_rcd_typ_cd
4234: AND nvl(ben_extract.gtt_rcd_typ_vals(i).low_lvl_cd,' ') = nvl(p_low_lvl_cd, ' ')
4235: THEN
4236:
4249: close c_rcd_in_file_flag ;
4250: -- Initialize array
4251: --
4252:
4253: hr_utility.set_location('l_rcd_rqd_flag'|| l_rcd_rqd_flag,10);
4254: g_val_tab := g_val_def;
4255: --
4256: open rcd_elmt_c ;
4257: fetch rcd_elmt_c bulk collect into
4314:
4315: end if ;
4316:
4317: if g_debug then
4318: hr_utility.set_location('short name '||elmt.short_name,90);
4319: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4320: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4321: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4322: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4315: end if ;
4316:
4317: if g_debug then
4318: hr_utility.set_location('short name '||elmt.short_name,90);
4319: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4320: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4321: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4322: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4323: hr_utility.set_location(' name '||elmt.name,90);
4316:
4317: if g_debug then
4318: hr_utility.set_location('short name '||elmt.short_name,90);
4319: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4320: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4321: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4322: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4323: hr_utility.set_location(' name '||elmt.name,90);
4324: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4317: if g_debug then
4318: hr_utility.set_location('short name '||elmt.short_name,90);
4319: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4320: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4321: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4322: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4323: hr_utility.set_location(' name '||elmt.name,90);
4324: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4325: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4318: hr_utility.set_location('short name '||elmt.short_name,90);
4319: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4320: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4321: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4322: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4323: hr_utility.set_location(' name '||elmt.name,90);
4324: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4325: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4326: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4319: hr_utility.set_location(' defined_balance_id '||elmt.defined_balance_id,90);
4320: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4321: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4322: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4323: hr_utility.set_location(' name '||elmt.name,90);
4324: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4325: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4326: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4327: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4320: hr_utility.set_location(' ttl_fnctn_cd '||elmt.ttl_fnctn_cd,90);
4321: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4322: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4323: hr_utility.set_location(' name '||elmt.name,90);
4324: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4325: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4326: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4327: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4328: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4321: hr_utility.set_location(' one_char_substr '||l_one_char_substr,90);
4322: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4323: hr_utility.set_location(' name '||elmt.name,90);
4324: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4325: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4326: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4327: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4328: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4329:
4322: hr_utility.set_location(' two_char_substr '||l_two_char_substr,90);
4323: hr_utility.set_location(' name '||elmt.name,90);
4324: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4325: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4326: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4327: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4328: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4329:
4330:
4323: hr_utility.set_location(' name '||elmt.name,90);
4324: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4325: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4326: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4327: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4328: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4329:
4330:
4331: end if ;
4324: hr_utility.set_location(' dflt_val '||elmt.dflt_val,90);
4325: hr_utility.set_location(' frmt_mask_lookup_cd '||elmt.frmt_mask_lookup_cd,90);
4326: hr_utility.set_location(' frmt_mask_cd '||l_frmt_mask_cd,90);
4327: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4328: hr_utility.set_location(' seq_num '||elmt.seq_num,90);
4329:
4330:
4331: end if ;
4332: --
4334: --
4335: -- check field include/suppress condition
4336: --
4337: g_elmt_name := elmt.name ;
4338: hr_utility.set_location('ELEMENT '||elmt.short_name,10);
4339: hr_utility.set_location('Type Code '||elmt.data_elmt_typ_cd,10);
4340:
4341: IF elmt.data_elmt_typ_cd in ('F','D') THEN
4342: --
4335: -- check field include/suppress condition
4336: --
4337: g_elmt_name := elmt.name ;
4338: hr_utility.set_location('ELEMENT '||elmt.short_name,10);
4339: hr_utility.set_location('Type Code '||elmt.data_elmt_typ_cd,10);
4340:
4341: IF elmt.data_elmt_typ_cd in ('F','D') THEN
4342: --
4343: -- data element is a field:
4359: end if ;
4360:
4361: --- decide the grouping element subhead
4362: if ben_ext_thread.g_ext_group_elmt1 is not null then
4363: hr_utility.set_location(' GROUP ' || ben_ext_thread.g_ext_group_elmt1 || ' / ' ||elmt.short_name , 99 ) ;
4364: if ben_ext_thread.g_ext_group_elmt1 = elmt.short_name then
4365: ben_ext_person.g_group_elmt_value1 := l_rslt_elmt ;
4366: end if ;
4367: if ben_ext_thread.g_ext_group_elmt2 is not null then
4374: else
4375: ben_ext_person.g_group_elmt_value2 := null ;
4376: end if ;
4377: end if ;
4378: hr_utility.set_location(' GROUP value ' || ben_ext_person.g_group_elmt_value1 , 99 ) ;
4379: else
4380: if p_rcd_typ_cd = 'S' then
4381: ben_ext_person.g_group_elmt_value1 := ' ' ;
4382: ben_ext_person.g_group_elmt_value2 := ' ' ;
4450: --- the max_string , the function down use the max lengh
4451: --- use the min of lenght and max lenght so format here only
4452: if elmt.max_length_num is not null then
4453: l_rslt_elmt := rpad(nvl(l_rslt_elmt,' '),elmt.max_length_num);
4454: hr_utility.set_location(' string max lenght '|| l_rslt_elmt,100 );
4455: end if ;
4456:
4457: ELSIF elmt.data_elmt_typ_cd = 'P' THEN
4458: -- payroll balance
4455: end if ;
4456:
4457: ELSIF elmt.data_elmt_typ_cd = 'P' THEN
4458: -- payroll balance
4459: hr_utility.set_location(' payroll balance id '|| elmt.defined_balance_id ,100 );
4460: -- l_rslt_elmt := '00.00';
4461: -- format mask handlinga
4462: if ben_Ext_person.g_assignment_id is not null then
4463: l_rslt_elmt := get_pay_balance(
4480: when others then
4481: null;
4482: end;
4483: end if ;
4484: hr_utility.set_location(' payroll balance '|| l_rslt_elmt,100 );
4485:
4486: END IF;
4487: --
4488: -- if resulting data element (field) is null, substitute it with
4506: l_rslt_elmt := SUBSTR(l_rslt_elmt, -l_max_len);
4507: else -- everything else truncs from the right.
4508: l_rslt_elmt := SUBSTR(l_rslt_elmt, 1, elmt.max_length_num);
4509: end if;
4510: hr_utility.set_location(' after max lenght '|| l_rslt_elmt,100 );
4511: --
4512: END IF;
4513: --
4514: --
4591: and ben_extract.gtt_rcd_rqd_vals_seq(rci).seq_num = ben_extract.gtt_rcd_typ_vals(i).seq_num
4592: )
4593: OR ben_extract.gtt_rcd_rqd_vals_seq(rci).low_lvl_cd = 'NOREQDRCD'
4594: THEN
4595: hr_utility.set_location('mandatory found' || p_low_lvl_cd , 99 );
4596: hr_utility.set_location('mandatory found' || ben_extract.gtt_rcd_typ_vals(i).seq_num , 99 );
4597: ben_extract.gtt_rcd_rqd_vals_seq(rci).rcd_found := TRUE;
4598: END IF;
4599: END LOOP ;
4592: )
4593: OR ben_extract.gtt_rcd_rqd_vals_seq(rci).low_lvl_cd = 'NOREQDRCD'
4594: THEN
4595: hr_utility.set_location('mandatory found' || p_low_lvl_cd , 99 );
4596: hr_utility.set_location('mandatory found' || ben_extract.gtt_rcd_typ_vals(i).seq_num , 99 );
4597: ben_extract.gtt_rcd_rqd_vals_seq(rci).rcd_found := TRUE;
4598: END IF;
4599: END LOOP ;
4600:
4640: --l_scnd_sort_val := ' ' ;
4641: end if ;
4642:
4643: --
4644: hr_utility.set_location( ' l_chg_rcd_upd_flag ' || p_data_typ_cd || ' / ' || l_chg_rcd_upd_flag, 99 ) ;
4645: l_ext_chg_rcd_mode := 'C' ;
4646: if p_data_typ_cd = 'C' and l_chg_rcd_upd_flag = 'Y' then
4647: -- of the record to be sonsoldated for change event extract
4648:
4955: ,p_object_version_number => l_object_version_number
4956: ,p_ext_chg_rcd_mode => l_ext_chg_rcd_mode
4957: ,p_ext_rcd_in_file_id => l_ext_rcd_in_file_id
4958: );
4959: hr_utility.set_location(' Mode ' || l_ext_chg_rcd_mode,99 ) ;
4960: end if ;
4961:
4962: -- if change extract and update flag defined and mode is update then
4963: if p_data_typ_cd = 'C' and l_chg_rcd_upd_flag = 'Y' and l_ext_chg_rcd_mode = 'U' then
5608: --
5609: --
5610: end if; --write record = 'Y'
5611: /* madatory validated down Else
5612: hr_utility.set_location(' Element not found ', 15);
5613: --- If the element is mandatory and null and the record is mandatory
5614: --- skip the person
5615: if nvl(l_rcd_rqd_flag,'N') = 'Y' then
5616: hr_utility.set_location(' record not found ', 15) ;
5612: hr_utility.set_location(' Element not found ', 15);
5613: --- If the element is mandatory and null and the record is mandatory
5614: --- skip the person
5615: if nvl(l_rcd_rqd_flag,'N') = 'Y' then
5616: hr_utility.set_location(' record not found ', 15) ;
5617: raise ben_ext_person.detail_error ;
5618: end if ;
5619: */
5620: end if ;
5624: --
5625: END LOOP;
5626:
5627: --
5628: hr_utility.set_location(' Exiting:'||l_proc, 15);
5629: --
5630: end process_ext_recs;
5631: --
5632: -- The following three functions are overloaded.
5645:
5646: --
5647: Begin
5648: --
5649: hr_utility.set_location('Entering:'||l_proc, 5);
5650: --
5651: if p_value is null then
5652: return p_value;
5653: end if;
5664: end if;
5665: --
5666: return (l_fmt_date);
5667: --
5668: hr_utility.set_location(' Exiting:'||l_proc, 15);
5669: --
5670: End apply_format_mask;
5671: --
5672: -- ----------------------------------------------------------------------------
5682: l_format varchar2(100);
5683: --
5684: Begin
5685: --
5686: hr_utility.set_location('Entering:'||l_proc, 10);
5687: --
5688: if p_value is null then
5689: return p_value;
5690: end if;
5690: end if;
5691: --
5692: if p_format_mask is not null then
5693: --
5694: hr_utility.set_location('format :'||p_format_mask, 10);
5695: hr_utility.set_location('value :'||p_value , 10);
5696: -- Check for special format
5697: --
5698: if substr(p_format_mask,length(p_format_mask),1) in ('{','}') then
5691: --
5692: if p_format_mask is not null then
5693: --
5694: hr_utility.set_location('format :'||p_format_mask, 10);
5695: hr_utility.set_location('value :'||p_value , 10);
5696: -- Check for special format
5697: --
5698: if substr(p_format_mask,length(p_format_mask),1) in ('{','}') then
5699: -- check the first char of the code
5705: if l_cd_strt = 'N' then
5706:
5707: --- convert the number as per the format
5708: l_format := substr(p_format_mask,1, length(p_format_mask)-1) ;
5709: hr_utility.set_location('l_format :'||l_format , 10);
5710:
5711: l_str_value := to_char(p_value,l_format);
5712:
5713: hr_utility.set_location('Formated number :'||l_str_value , 10);
5709: hr_utility.set_location('l_format :'||l_format , 10);
5710:
5711: l_str_value := to_char(p_value,l_format);
5712:
5713: hr_utility.set_location('Formated number :'||l_str_value , 10);
5714:
5715: -- as per the bug 5387355 , The Last digit of the number has to be changed
5716: -- if the number is + then 1 to 9 changes as A to I and 0 chages as {
5717: -- if the number is - then 1 to 9 changes as J to R and 0 chages as }
5747: '1','J','2','K','3','L','4','M','5','N','6','O','7','P','8','Q','9','R','0','}')
5748: into l_fmt_num from dual;
5749: end if;
5750:
5751: hr_utility.set_location('non Formated value :'||l_str_value , 10);
5752: hr_utility.set_location('non Formated value :'|| (length(l_str_value)-1) , 10);
5753:
5754: l_fmt_num := substr(l_str_value ,1, (length(l_str_value)-1) )||l_fmt_num;
5755:
5748: into l_fmt_num from dual;
5749: end if;
5750:
5751: hr_utility.set_location('non Formated value :'||l_str_value , 10);
5752: hr_utility.set_location('non Formated value :'|| (length(l_str_value)-1) , 10);
5753:
5754: l_fmt_num := substr(l_str_value ,1, (length(l_str_value)-1) )||l_fmt_num;
5755:
5756: hr_utility.set_location('Formated value :'||l_fmt_num , 10);
5752: hr_utility.set_location('non Formated value :'|| (length(l_str_value)-1) , 10);
5753:
5754: l_fmt_num := substr(l_str_value ,1, (length(l_str_value)-1) )||l_fmt_num;
5755:
5756: hr_utility.set_location('Formated value :'||l_fmt_num , 10);
5757:
5758: end if;
5759: else --- for {} formats
5760: --
5769: end if;
5770: --
5771: return (rtrim(ltrim(l_fmt_num)));
5772: --
5773: hr_utility.set_location(' Exiting:'||l_proc, 10);
5774: --
5775: End apply_format_mask;
5776: --
5777: -- ----------------------------------------------------------------------------
5787: l_err_message varchar2(2000) ;
5788: --
5789: Begin
5790: --
5791: hr_utility.set_location('Entering:'||l_proc, 15);
5792: hr_utility.set_location('value :'||p_value||' - ' || p_format_mask, 15);
5793: --
5794: if p_value is null then
5795: return p_value;
5788: --
5789: Begin
5790: --
5791: hr_utility.set_location('Entering:'||l_proc, 15);
5792: hr_utility.set_location('value :'||p_value||' - ' || p_format_mask, 15);
5793: --
5794: if p_value is null then
5795: return p_value;
5796: end if;
5799: l_err_message := get_error_msg(92065,'BEN_92065_EXT_FRMT_INVALID',g_elmt_name );
5800: --
5801: if p_format_mask is not null then
5802: --
5803: hr_utility.set_location('format mask :' || p_format_mask, 16);
5804: l_value_stripped := translate(p_value,
5805: '1234567890 !@#$%^&*()-_+={}|:"<>?,./;''[]\`~',
5806: '1234567890');
5807: --
5977: else
5978: --
5979: l_fmt_string := p_value;
5980: --
5981: hr_utility.set_location('value :' || l_fmt_string, 16);
5982: end if;
5983: --
5984: return (rtrim(ltrim(l_fmt_string)));
5985: --
5982: end if;
5983: --
5984: return (rtrim(ltrim(l_fmt_string)));
5985: --
5986: hr_utility.set_location(' Exiting:'||l_proc, 15);
5987: --
5988: End apply_format_mask;
5989:
5990: -- ------------------------------------------------------------------
5999: l_err_message varchar2(2000) ;
6000: --
6001: Begin
6002: --
6003: hr_utility.set_location('Entering:'||l_proc, 15);
6004: hr_utility.set_location('value :'||p_value||' - ' || p_format_mask, 15);
6005: --
6006: if p_value is null then
6007: return p_value;
6000: --
6001: Begin
6002: --
6003: hr_utility.set_location('Entering:'||l_proc, 15);
6004: hr_utility.set_location('value :'||p_value||' - ' || p_format_mask, 15);
6005: --
6006: if p_value is null then
6007: return p_value;
6008: end if;
6038: );
6039: --
6040: end if;
6041: end if ;
6042: hr_utility.set_location(' Exiting:'||l_proc, 15);
6043: return l_fmt_string ;
6044: end apply_format_function ;
6045:
6046:
6077: ;
6078: --
6079: Begin
6080: --
6081: hr_utility.set_location('Entering:'||l_proc, 5);
6082: --
6083: if p_ext_rcd_in_file_id is not null then
6084: --
6085: open incl_rcd_c;
6101: end if;
6102: --
6103: return (l_sprs_or_incl);
6104: --
6105: hr_utility.set_location(' Exiting:'||l_proc, 15);
6106: --
6107: End sprs_or_incl;
6108: --
6109: --