1 PACKAGE BODY PAY_PAYUSDED_XMLP_PKG AS
2 /* $Header: PAYUSDEDB.pls 120.0 2007/12/28 06:44:11 srikrish noship $ */
3
4 function BeforeReport return boolean is
5
6 l_trace varchar2(1);
7 l_message varchar2(240);
8 l_org_name varchar2(240);
9
10 cursor c_trace is
11 select 'x'
12 from pay_action_parameters
13 where parameter_name = 'TRACE'
14 and parameter_value = 'Y';
15
16 cursor c_org_name (cp_org_id in number) is
17 select name
18 from hr_organization_units
19 where organization_id = cp_org_id;
20
21 begin
22
23 P_SORT_OPTION1:=nvl(P_SORT_OPTION1,'GRE');
24 --hr_standard.event('BEFORE REPORT');
25
26
27 open c_trace;
28 fetch c_trace into l_trace;
29 if c_trace%found then
30 /*srw.do_sql('alter session set SQL_TRACE TRUE');null;*/
31 execute immediate 'alter session set SQL_TRACE TRUE';
32
33 end if;
34 close c_trace;
35
36
37
38 open c_org_name (p_business_group_id);
39 fetch c_org_name into l_org_name;
40 if c_org_name%found then
41 c_business_group_name := l_org_name;
42 else
43 c_business_group_name := '';
44 end if;
45 close c_org_name;
46
47
48 if(p_tax_unit_id is not null) then
49 l_message := 'Error: While Selecting Government Reporting Entity ....';
50 open c_org_name (p_tax_unit_id);
51 fetch c_org_name into l_org_name;
52 if c_org_name%found then
53 c_gre := l_org_name;
54 else
55 c_gre := '';
56 end if;
57 close c_org_name;
58 end if;
59
60
61 if(p_organization_id is not null )
62 then
63 l_message := 'Error: While Selecting Organization name ....';
64 open c_org_name (p_organization_id);
65 fetch c_org_name into l_org_name;
66 if c_org_name%found then
67 c_organization := l_org_name;
68 else
69 c_organization := '';
70 end if;
71 close c_org_name;
72 end if;
73
74
75 BEGIN
76
77
78 if(p_consolidation_set_id is not null)
79 then
80 l_message := 'Error: While Selecting Consolidation_set_name ....';
81 select consolidation_set_name
82 into c_consolidation_set
83 from pay_consolidation_sets
84 where consolidation_set_id = p_consolidation_set_id
85 and business_group_id = p_business_group_id;
86 else
87 c_consolidation_set :='';
88 end if;
89
90
91
92 if (p_payroll_id is not null)
93 then
94 l_message := 'Error: While Selecting Payroll name ....';
95 select payroll_name
96 into c_payroll
97 from pay_all_payrolls_f
98 where payroll_id = p_payroll_id
99 and p_end_date between effective_start_date and effective_end_date
100 and business_group_id = p_business_group_id;
101 else
102 c_payroll := '';
103 end if;
104
105
106
107 if(p_element_set_id is not null)
108 then
109 l_message := 'Error: While Selecting Element Set name ....';
110 select element_set_name
111 into c_element_set
112 from pay_element_sets
113 where element_set_id = p_element_set_id;
114 else
115 c_element_set := '';
116 end if;
117
118
119
120
121
122 if(p_classification_id is not null)
123 then
124 l_message := 'Error: While Selecting Classification name ....';
125 select classification_name
126 into c_classification
127 from pay_element_classifications
128 where classification_id = p_classification_id;
129 else
130 c_classification := '';
131 end if;
132
133
134
135 if(p_element_type_id is not null)
136 then
137 l_message := 'Error: While Selecting Element name ....';
138 select element_name
139 into c_element_name
140 from pay_element_types_f
141 where element_type_id = p_element_type_id
142 and p_end_date between effective_start_date and effective_end_date;
143 else
144 c_element_name := '';
145 end if;
146
147
148
149 if (p_location_id is not null)
150 then
151 l_message := 'Error: While Selecting Location name ....';
152 select location_code
153 into c_location
154 from hr_locations
155 where location_id = p_location_id;
156 else
157 c_location := '';
158 end if;
159
160
161 if(p_person_id is not null)
162 then
163 l_message := 'Error: While Selecting Employee name ....';
164 select full_name
165 into c_person
166 from per_people_f
167 where person_id = p_person_id
168 and p_end_date between effective_start_date and effective_end_date;
169 else
170 c_person := '';
171 end if;
172 EXCEPTION
173 when NO_DATA_FOUND then
174 /*srw.message(11,l_message);*/null;
175
176 /*srw.message(11,'No Data Found');*/null;
177
178 return (FALSE);
179 when OTHERS then
180 /*srw.message(11,l_message);*/null;
181
182 return (FALSE);
183 END;
184 return (TRUE);
185 end;
186
187 function C_REPORT_SUBTITLEFormula return VARCHAR2 is
188 begin
189 return null;
190
191 end;
192
193 function scheduled_dednformula(primary_balance in number, not_taken_balance in number, arrears_taken in number) return number is
194 begin
195
196 return (nvl(primary_balance,0) + nvl(not_taken_balance,0)- nvl(arrears_taken,0));
197 end;
198
199 function current_arrearsformula(arrears_balance in number) return number is
200 l_current_arrears number := 0;
201 begin
202
203 if nvl(arrears_balance,0) >= 0 then
204 l_current_arrears := nvl(arrears_balance,0);
205 end if;
206
207 return l_current_arrears;
208
209 end;
210
211 function arrears_takenformula(arrears_balance in number) return number is
212 l_arrears_taken number := 0;
213 begin
214 if nvl(arrears_balance,0) < 0 then
215 l_arrears_taken := -1 * (nvl(arrears_balance,0));
216 end if;
217
218 return l_arrears_taken;
219
220 end;
221
222 function remainingformula(total_owed in number, CF_Accrued in number) return number is
223 diff_value number;
224 begin
225 diff_value := to_number(nvl(total_owed,0)) - to_number(nvl(CF_Accrued,0)) ;
226 return diff_value;
227
228 end;
229
230 function element_total_textformula(element_name in varchar2) return varchar2 is
231 begin
232 return substr(element_name,1,87) || ' Total';
233 end;
234
235 function classification_total_textformu(classification_name in varchar2) return varchar2 is
236 begin
237 return substr(classification_name,1,87) || ' Total';
238 end;
239
240 function s3_total_textformula(sort_option1_value in varchar2, sort_option2_value in varchar2, sort_option3_value in varchar2) return varchar2 is
241 begin
242 return sort_option1_value ||' / '|| sort_option2_value || ' / '|| sort_option3_value || ' Total';
243 end;
244
245 function s2_total_textformula(sort_option1_value in varchar2, sort_option2_value in varchar2) return varchar2 is
246 begin
247 return sort_option1_value ||' / '||sort_option2_value || ' Total';
248 end;
249
250 function s1_total_textformula(sort_option1_value in varchar2) return varchar2 is
251 begin
252 return sort_option1_value || ' Total';
253 end;
254
255 function person_total_textformula(full_name in varchar2) return varchar2 is
256 begin
257 return substr(full_name,1,247) || ' Total';
258 end;
259
260 function cf_sort1formula(Sort_option1 in varchar2) return varchar2 is
261 begin
262 return (Substr(Sort_option1,1,30));
263 end;
264
265 function cf_sort2formula(Sort_option2 in varchar2) return varchar2 is
266 begin
267 return (Substr(Sort_option2,1,30));
268 end;
269
270 function cf_sort3formula(Sort_option3 in varchar2) return varchar2 is
271 begin
272 return (Substr(Sort_option3,1,30));
273 end;
274
275 function cf_sort1_valueformula(Sort_option1_value in varchar2) return varchar2 is
276 begin
277 return (Substr(Sort_option1_value,1,30));
278 end;
279
280 function cf_sort2_valueformula(Sort_option2_value in varchar2) return varchar2 is
281 begin
282 return (Substr(Sort_option2_value,1,30));
283 end;
284
285 function cf_sort3_valueformula(Sort_option3_value in varchar2) return varchar2 is
286 begin
287 return (Substr(Sort_option3_value,1,30));
288 end;
289
290 function cf_accruedformula(accrued_balance in varchar2, Primary_balance in number, Total_owed in number) return number is
291 begin
292 if( (nvl(accrued_balance,0) = 0) and (nvl(Primary_balance,0) <> 0) )
293 then
294 return Total_owed;
295 else
296 return Accrued_balance;
297 end if;
298 RETURN NULL; end;
299
300 function s3_textformula(sort_option1_value in varchar2, Sort_option1 in varchar2, sort_option2_value in varchar2, Sort_option2 in varchar2, sort_option3_value in varchar2, Sort_option3 in varchar2) return varchar2 is
301 retval varchar2(240);
302 begin
303 if(sort_option1_value is not null)
304 then
305 retval := Sort_option1 || ': '||sort_option1_value ||' ';
306 end if;
307
308 if(sort_option2_value is not null)
309 then
310 retval := retval || Sort_option2 || ': '||sort_option2_value ||' ';
311 end if;
312
313 if(sort_option3_value is not null)
314 then
315 retval := retval || Sort_option3 || ': '||sort_option3_value ||' ';
316 end if;
317
318 return retval;
319 end;
320
321 function AfterPForm return boolean is
322
323 cursor c_element_set(cp_element_set_id in number) is
324 select petr.element_type_id
325 from pay_element_type_rules petr
326 where petr.element_set_id = cp_element_set_id
327 and petr.include_or_exclude = 'I'
328 union all
329 select pet1.element_type_id
330 from pay_element_types_f pet1
331 where pet1.classification_id in
332 (select classification_id
333 from pay_ele_classification_rules
334 where element_set_id = cp_element_set_id)
335 minus
336 select petr.element_type_id
337 from pay_element_type_rules petr
338 where petr.element_set_id = cp_element_set_id
339 and petr.include_or_exclude = 'E';
340
341 lv_element_set_where varchar2(32000);
342 ln_element_type_id number;
343
344 begin
345
346 p_hint := ' ';
347
348 if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
349 p_hint := '';
350 end if;
351
352
353 if p_person_id is not null then
354 p_where_clause := 'and to_number(person_id) = to_number(:P_PERSON_ID) ';
355 p_hint := ' ';
356 end if;
357
358 if p_payroll_id is not null then
359 p_where_clause := p_where_clause ||
360 ' and payroll_id = to_number(:p_payroll_id) ';
361 end if;
362
363 if p_classification_id is not null then
364 p_where_clause := p_where_clause ||
365 ' and classification_id = to_number(:P_CLASSIFICATION_ID) ';
366 end if;
367
368 if p_tax_unit_id is not null then
369 p_where_clause := p_where_clause ||
370 ' and tax_unit_id = to_number(:P_TAX_UNIT_ID) ';
371 end if;
372
373 if p_element_type_id is not null then
374 p_where_clause := p_where_clause ||
375 ' and element_type_id = to_number(:P_ELEMENT_TYPE_ID) ';
376 end if;
377
378 if p_organization_id is not null then
379 p_where_clause := p_where_clause ||
380 ' and organization_id = to_number(:P_ORGANIZATION_ID) ';
381 end if;
382
383 if p_location_id is not null then
384 p_where_clause := p_where_clause ||
385 ' and location_id = to_number(:P_LOCATION_ID) ';
386 end if;
387
388 if p_element_set_id is not null then
389 open c_element_set(p_element_set_id);
390 loop
391 fetch c_element_set into ln_element_type_id;
392 if c_element_set%notfound then
393
394 lv_element_set_where := substr(lv_element_set_where, 2);
395 exit;
396 end if;
397 lv_element_Set_where := lv_element_set_where || ',' || ln_element_type_id;
398 end loop;
399 close c_element_set;
400
401 p_where_clause := p_where_clause ||
402 ' and element_type_id in (' || lv_element_set_where || ')';
403 end if;
404
405
406
407 return (TRUE);
408 end;
409
410 function AfterReport return boolean is
411 begin
412
413 --hr_standard.event('AFTER REPORT');
414 return (TRUE);
415 end;
416
417 --Functions to refer Oracle report placeholders--
418
419 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
420 Begin
421 return C_BUSINESS_GROUP_NAME;
422 END;
423 Function C_REPORT_SUBTITLE_p return varchar2 is
424 Begin
425 return C_REPORT_SUBTITLE;
426 END;
427 Function C_Consolidation_set_p return varchar2 is
428 Begin
429 return C_Consolidation_set;
430 END;
431 Function C_Payroll_p return varchar2 is
432 Begin
433 return C_Payroll;
434 END;
435 Function C_Classification_p return varchar2 is
436 Begin
437 return C_Classification;
438 END;
439 Function C_Element_name_p return varchar2 is
440 Begin
441 return C_Element_name;
442 END;
443 Function C_GRE_p return varchar2 is
444 Begin
445 return C_GRE;
446 END;
447 Function C_Location_p return varchar2 is
448 Begin
449 return C_Location;
450 END;
451 Function C_Organization_p return varchar2 is
452 Begin
453 return C_Organization;
454 END;
455 Function C_Person_p return varchar2 is
456 Begin
457 return C_Person;
458 END;
459 Function C_ELEMENT_SET_p return varchar2 is
460 Begin
461 return C_ELEMENT_SET;
462 END;
463 END PAY_PAYUSDED_XMLP_PKG ;
[Home] [Help]