[Home] [Help]
PACKAGE BODY: APPS.PER_PERFRD2R_XMLP_PKG
Source
1 PACKAGE BODY PER_PERFRD2R_XMLP_PKG AS
2 /* $Header: PERFRD2RB.pls 120.0 2007/12/24 13:18:20 amakrish noship $ */
3
4 function c_set_unitsformula(cotorep_category in varchar2, age in number, previous_cotorep in varchar2, disability_rate in number, due_to_wa in varchar2, disability_class_code in varchar2, pid in number) return number is
5 l_total_cotorep number;
6 l_total_accident number;
7 l_total_other number;
8 pos1 integer;
9 pos2 integer;
10 l_base_unit number;
11 l_xcot_a number;
12 l_xcot_b number;
13 l_xcot_c number;
14 l_xcot_young_age number;
15 l_xcot_old_age number;
16 l_xcot_age_units number;
17 l_xcot_training_hours number;
18 l_xcot_training_units number;
19 l_xcot_ap number;
20 l_xcot_impro number;
21 l_xcot_cat number;
22 l_xcot_cdtd number;
23 l_xcot_cfp number;
24 l_xipp_low_rate number;
25 l_xipp_medium_rate number;
26 l_xipp_high_rate number;
27 l_xipp_low_units number;
28 l_xipp_medium_units number;
29 l_xipp_high_units number;
30 l_hire_units number;
31 begin
32 per_fr_d2_pkg.get_extra_units(p_establishment_id,
33 pc_1jan,
34 l_base_unit,
35 l_xcot_a,
36 l_xcot_b,
37 l_xcot_c,
38 l_xcot_young_age,
39 l_xcot_old_age,
40 l_xcot_age_units,
41 l_xcot_training_hours,
42 l_xcot_training_units,
43 l_xcot_ap,
44 l_xcot_impro,
45 l_xcot_cat,
46 l_xcot_cdtd,
47 l_xcot_cfp,
48 l_xipp_low_rate,
49 l_xipp_medium_rate,
50 l_xipp_high_rate,
51 l_xipp_low_units,
52 l_xipp_medium_units,
53 l_xipp_high_units,
54 l_hire_units);
55 l_total_cotorep := l_base_unit;
56 if not(cotorep_category is null) then
57 if (cotorep_category = 'A') then
58 l_total_cotorep := l_total_cotorep + l_xcot_a;
59 elsif (cotorep_category = 'B') then
60 l_total_cotorep := l_total_cotorep + l_xcot_b;
61 elsif (cotorep_category = 'C') then
62 l_total_cotorep := l_total_cotorep + l_xcot_c;
63 end if;
64 if (age >= l_xcot_old_age) or (age < l_xcot_young_age) then
65 l_total_cotorep := l_total_cotorep + l_xcot_age_units;
66 end if;
67 if not(pc_hours_training is null)
68 and (pc_hours_training >= l_xcot_training_hours)
69 then
70 l_total_cotorep := l_total_cotorep + l_xcot_training_units;
71 end if;
72 if previous_cotorep in ('AP','IMPRO','CAT','CDTD','CFP') then
73 if (previous_cotorep = 'AP') then
74 l_total_cotorep := l_total_cotorep + l_xcot_ap;
75 elsif (previous_cotorep = 'IMPRO') then
76 l_total_cotorep := l_total_cotorep + l_xcot_impro;
77 elsif (previous_cotorep = 'CAT') then
78 l_total_cotorep := l_total_cotorep + l_xcot_cat;
79 elsif (previous_cotorep = 'CDTD') then
80 l_total_cotorep := l_total_cotorep + l_xcot_cdtd;
81 elsif (pc_hire_year = p_year or pc_hire_year = p_year-1) then
82 l_total_cotorep := l_total_cotorep + l_xcot_cfp;
83 end if;
84 end if;
85 if (pc_year_became_permanent = p_year or
86 pc_year_became_permanent = p_year-1) then
87 l_total_cotorep := l_total_cotorep + l_hire_units; end if;
88 end if;
89 l_total_accident := l_base_unit;
90 if not(disability_rate is null) and (due_to_wa = 'Y') then
91 if (disability_rate >= l_xipp_low_rate)
92 and (disability_rate < l_xipp_medium_rate)
93 then
94 l_total_accident := l_total_accident + l_xipp_low_units;
95 elsif (disability_rate >= l_xipp_medium_rate)
96 and (disability_rate <= l_xipp_high_rate)
97 then
98 l_total_accident := l_total_accident + l_xipp_medium_units;
99 elsif (disability_rate > l_xipp_high_rate) then
100 l_total_accident := l_total_accident + l_xipp_high_units;
101 end if;
102 if (pc_year_became_permanent = p_year or
103 pc_year_became_permanent = p_year-1) then
104 l_total_accident := l_total_accident + l_hire_units;
105 end if;
106 end if;
107 l_total_other := l_base_unit;
108 if disability_class_code in ('CIVIL','MILITARY','MILITARY_EQUIVALENT') then
109 if (pc_year_became_permanent = p_year or
110 pc_year_became_permanent = p_year-1) then
111 l_total_other := l_total_other + l_hire_units;
112 end if;
113 end if;
114 pc_units_total := greatest(l_total_cotorep,l_total_accident,l_total_other);
115 pos1 := instr (pc_count_disabled,to_char(pid)||'=');
116 if pos1 = 0 then
117 pc_units_coef := 0;
118 else
119 pos1 := pos1 + NVL(length(to_char(pid)), 0) +1;
120 pos2 := instr (pc_count_disabled,';',pos1);
121 if pos2 = 0 then
122 pos2 := NVL(length(pc_count_disabled), 0) +1;
123 end if;
124 pc_units_coef := to_number(substr(pc_count_disabled,pos1,pos2-pos1));
125 end if;
126 pc_units_actual := round(pc_units_total*pc_units_coef,2);
127 return(1);
128 end;
129
130 function AfterPForm return boolean is
131 begin
132 PC_1JAN := to_date (to_char(P_YEAR)||'/01/01','YYYY/MM/DD');
133 PC_31DEC := to_date (to_char(P_YEAR)||'/12/31','YYYY/MM/DD');
134 PC_31DEC_m := to_char(PC_31DEC, 'dd-mon-yyyy');
135 return (TRUE);
136 end;
137
138 function BeforeReport return boolean is
139 l_return_code integer;
140 l_headcount_obligation number;
141 l_headcount_particular number;
142 l_basis_obligation number;
143 l_obligation number;
144 l_breakdown_particular varchar2(32000);
145 l_count_disabled varchar2(32000);
146 l_disabled_where_clause varchar2(32500);
147 begin
148 --Commented By Raj hr_standard.event('BEFORE REPORT');
149 l_return_code := per_fr_d2_pkg.set_headcounts
150 (P_ESTABLISHMENT_ID,
151 PC_1JAN,
152 PC_31DEC,
153 l_headcount_obligation,
154 l_headcount_particular,
155 l_basis_obligation,
156 l_obligation,
157 l_breakdown_particular,
158 l_count_disabled,
159 l_disabled_where_clause);
160 PC_HEADCOUNT_OBLIGATION := l_headcount_obligation;
161 PC_HEADCOUNT_PARTICULAR := l_headcount_particular;
162 PC_BASIS_OBLIGATION := l_basis_obligation;
163 PC_OBLIGATION := l_obligation;
164 PC_BREAKDOWN_PARTICULAR := l_breakdown_particular;
165 PC_COUNT_DISABLED := l_count_disabled;
166 PC_DISABLED_WHERE_CLAUSE := l_disabled_where_clause;
167 return (TRUE);
168 end;
169
170 function fc_set_job_infoformula(pid in number) return number is
171 l_pcs_code varchar2(10);
172 l_job_title varchar2(240);
173 l_hours_training number;
174 l_hire_year number;
175 l_year_became_permanent number;
176 begin
177 per_fr_d2_pkg.get_job_info(p_establishment_id,
178 pid,
179 pc_1jan,
180 pc_31dec,
181 p_year,
182 l_pcs_code,
183 l_job_title,
184 l_hours_training,
185 l_hire_year,
186 l_year_became_permanent);
187 pc_pcs_code := l_pcs_code;
188 pc_job_title := l_job_title;
189 pc_hours_training := l_hours_training;
190 pc_hire_year := l_hire_year;
191 pc_year_became_permanent := l_year_became_permanent;
192 return(1);
193 end;
194
195 function g_disabled_empgroupfilter(disability_start_date in date, disability_end_date in date) return boolean is
196 begin
197 if nvl(disability_start_date,to_date('1900/01/01','YYYY/MM/DD')) <= pc_31dec
198 and nvl(disability_end_date,to_date('4712/12/31','YYYY/MM/DD')) >= pc_1jan
199 then
200 return (TRUE);
201 else
202 return (FALSE);
203 end if;
204 RETURN NULL; end;
205
206 function AfterReport return boolean is
207 begin
208 --Commented By Raj hr_standard.event('AFTER REPORT');
209 return (TRUE);
210
211 end;
212
213 --Functions to refer Oracle report placeholders--
214
215 Function PC_HIRE_YEAR_p return number is
216 Begin
217 return PC_HIRE_YEAR;
218 END;
219 Function PC_YEAR_BECAME_PERMANENT_p return number is
220 Begin
221 return PC_YEAR_BECAME_PERMANENT;
222 END;
223 Function PC_JOB_TITLE_p return varchar2 is
224 Begin
225 return PC_JOB_TITLE;
226 END;
227 Function PC_HOURS_TRAINING_p return number is
228 Begin
229 return PC_HOURS_TRAINING;
230 END;
231 Function PC_PCS_CODE_p return varchar2 is
232 Begin
233 return PC_PCS_CODE;
234 END;
235 Function PC_UNITS_TOTAL_p return number is
236 Begin
237 return PC_UNITS_TOTAL;
238 END;
239 Function PC_UNITS_COEF_p return number is
240 Begin
241 return PC_UNITS_COEF;
242 END;
243 Function PC_UNITS_ACTUAL_p return number is
244 Begin
245 return PC_UNITS_ACTUAL;
246 END;
247 Function PC_DISABLED_WHERE_CLAUSE_p return varchar2 is
248 Begin
249 return PC_DISABLED_WHERE_CLAUSE;
250 END;
251 Function PC_HEADCOUNT_OBLIGATION_p return number is
252 Begin
253 return PC_HEADCOUNT_OBLIGATION;
254 END;
255 Function PC_HEADCOUNT_PARTICULAR_p return number is
256 Begin
257 return PC_HEADCOUNT_PARTICULAR;
258 END;
259 Function PC_BASIS_OBLIGATION_p return number is
260 Begin
261 return PC_BASIS_OBLIGATION;
262 END;
263 Function PC_OBLIGATION_p return number is
264 Begin
265 return PC_OBLIGATION;
266 END;
267 Function PC_BREAKDOWN_PARTICULAR_p return varchar2 is
268 Begin
269 return PC_BREAKDOWN_PARTICULAR;
270 END;
271 Function PC_COUNT_DISABLED_p return varchar2 is
272 Begin
273 return PC_COUNT_DISABLED;
274 END;
275 END PER_PERFRD2R_XMLP_PKG ;