1 PACKAGE BODY PER_PERRPRAA_XMLP_PKG AS
2 /* $Header: PERRPRAAB.pls 120.1 2007/12/06 11:30:06 amakrish noship $ */
3 function BeforeReport return boolean is
4 begin
5 P_SESSION_DATE1:=TO_CHAR(P_SESSION_DATE,'DD-MON-YYYY');
6 declare
7 v_organization_name varchar2(240);
8 v_organization_type varchar2(80);
9
10 begin
11
12
13 --hr_standard.event('BEFORE REPORT');
14
15 c_business_group_name :=
16 hr_reports.get_business_group(p_business_group_id);
17
18 IF p_person_id is null AND p_organization_id is null THEN
19 p_organization_id := p_business_group_id;
20 END IF;
21
22 hr_reports.get_organization(p_organization_id,v_organization_name,v_organization_type);
23 c_organization_name := v_organization_name;
24
25
26
27 IF p_person_id is not null THEN
28 c_person_name := hr_reports.get_person_name(p_session_date,p_person_id);
29 END IF;
30
31 c_abs_type_name1 := hr_reports.get_abs_type(p_abs_type1);
32 c_abs_type_name2 := hr_reports.get_abs_type(p_abs_type2);
33 c_abs_type_name3 := hr_reports.get_abs_type(p_abs_type3);
34 c_abs_type_name4 := hr_reports.get_abs_type(p_abs_type4);
35 c_abs_type_name5 := hr_reports.get_abs_type(p_abs_type5);
36 c_abs_type_name6 := hr_reports.get_abs_type(p_abs_type6);
37 c_abs_type_name7 := hr_reports.get_abs_type(p_abs_type7);
38 c_abs_type_name8 := hr_reports.get_abs_type(p_abs_type8);
39 c_abs_type_name9 := hr_reports.get_abs_type(p_abs_type9);
40 c_abs_type_name10 := hr_reports.get_abs_type(p_abs_type10);
41
42 c_display_abtypes := NULL;
43 c_absence_types := NULL;
44 c_abtypes_entered := 'N';
45
46 IF p_abs_type1 IS NOT NULL THEN
47 c_display_abtypes := c_abs_type_name1||',';
48 c_abtypes_entered := 'Y';
49 c_absence_types := p_abs_type1||',';
50 END IF;
51 IF p_abs_type2 IS NOT NULL THEN
52 c_display_abtypes := c_display_abtypes||c_abs_type_name2||',';
53 c_abtypes_entered := 'Y';
54 c_absence_types := c_absence_types||p_abs_type2||',';
55 END IF;
56 IF p_abs_type3 IS NOT NULL THEN
57 c_display_abtypes := c_display_abtypes||c_abs_type_name3||',';
58 c_abtypes_entered := 'Y';
59 c_absence_types := c_absence_types||p_abs_type3||',';
60 END IF;
61 IF p_abs_type4 IS NOT NULL THEN
62 c_display_abtypes := c_display_abtypes||c_abs_type_name4||',';
63 c_abtypes_entered := 'Y';
64 c_absence_types := c_absence_types||p_abs_type4||',';
65 END IF;
66 IF p_abs_type5 IS NOT NULL THEN
67 c_display_abtypes := c_display_abtypes||c_abs_type_name5||',';
68 c_abtypes_entered := 'Y';
69 c_absence_types := c_absence_types||p_abs_type5||',';
70 END IF;
71 IF p_abs_type6 IS NOT NULL THEN
72 c_display_abtypes := c_display_abtypes||c_abs_type_name6||',';
73 c_abtypes_entered := 'Y';
74 c_absence_types := c_absence_types||p_abs_type6||',';
75 END IF;
76 IF p_abs_type7 IS NOT NULL THEN
77 c_display_abtypes := c_display_abtypes||c_abs_type_name7||',';
78 c_abtypes_entered := 'Y';
79 c_absence_types := c_absence_types||p_abs_type7||',';
80 END IF;
81 IF p_abs_type8 IS NOT NULL THEN
82 c_display_abtypes := c_display_abtypes||c_abs_type_name8||',';
83 c_abtypes_entered := 'Y';
84 c_absence_types := c_absence_types||p_abs_type8||',';
85 END IF;
86 IF p_abs_type9 IS NOT NULL THEN
87 c_display_abtypes := c_display_abtypes||c_abs_type_name9||',';
88 c_abtypes_entered := 'Y';
89 c_absence_types := c_absence_types||p_abs_type9||',';
90 END IF;
91 IF p_abs_type10 IS NOT NULL THEN
92 c_display_abtypes := c_display_abtypes||c_abs_type_name10||',';
93 c_abtypes_entered := 'Y';
94 c_absence_types := c_absence_types||p_abs_type10||',';
95 END IF;
96 c_absence_types := rtrim(c_absence_types,',');
97 c_display_abtypes := rtrim(c_display_abtypes,',');
98 IF c_absence_types IS NOT NULL then
99 p_absence_sql := 'and aat.absence_attendance_type_id in ('||c_absence_types||')';
100 p_absence_att_sql := 'and aa.absence_attendance_type_id in ('||c_absence_types||')';
101 END IF;
102
103
104 select value
105 into c_nls_language
106 from nls_session_parameters
107 where parameter = 'NLS_LANGUAGE';
108
109
110 end; return (TRUE);
111 end;
112
113 function c_running_totalformula(summ_person_id in number, p_absence_attendance_type_id in number) return number is
114 begin
115
116 declare l_runtot NUMBER(12,2);
117 begin
118 begin
119
120
121 SELECT sum (fnd_number.canonical_to_number(target.screen_entry_value))
122 INTO l_runtot
123 FROM pay_element_entry_values_f target
124 , pay_element_entries_f ee
125 , per_absence_attendance_types abtype
126 , per_assignments_f ASS
127 WHERE ASS.person_id = summ_person_id
128 AND ASS.primary_flag = 'Y'
129 AND P_SESSION_DATE
130 BETWEEN ASS.effective_start_date AND ASS.effective_end_date
131 AND ass.assignment_id = ee.assignment_id
132 AND target.element_entry_id = EE.element_entry_id
133 AND ABTYPE.absence_attendance_type_id = p_absence_attendance_type_id
134 AND ABTYPE.input_value_id = target.input_value_id;
135 EXCEPTION
136 when no_data_found then null;
137 end;
138 return(l_runtot);
139 end;
140 RETURN NULL; end;
141
142 function AfterReport return boolean is
143 begin
144
145 --hr_standard.event('AFTER REPORT');
146
147 return (TRUE);
148 end;
149
150 function AfterPForm return boolean is
151 begin
152
153 return (TRUE);
154 end;
155
156 function BetweenPage return boolean is
157 begin
158
159 return (TRUE);
160 end;
161
162 --Functions to refer Oracle report placeholders--
163
164 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
165 Begin
166 return C_BUSINESS_GROUP_NAME;
167 END;
168 Function C_REPORT_SUBTITLE_p return varchar2 is
169 Begin
170 return C_REPORT_SUBTITLE;
171 END;
172 Function C_DISPLAY_ABTYPES_p return varchar2 is
173 Begin
174 return C_DISPLAY_ABTYPES;
175 END;
176 Function C_ORGANIZATION_NAME_p return varchar2 is
177 Begin
178 return C_ORGANIZATION_NAME;
179 END;
180 Function C_ABTYPES_ENTERED_p return varchar2 is
181 Begin
182 return C_ABTYPES_ENTERED;
183 END;
184 Function C_ABSENCE_TYPES_p return varchar2 is
185 Begin
186 return C_ABSENCE_TYPES;
187 END;
188 Function C_ABS_TYPE_NAME1_p return varchar2 is
189 Begin
190 return C_ABS_TYPE_NAME1;
191 END;
192 Function C_ABS_TYPE_NAME2_p return varchar2 is
193 Begin
194 return C_ABS_TYPE_NAME2;
195 END;
196 Function C_ABS_TYPE_NAME3_p return varchar2 is
197 Begin
198 return C_ABS_TYPE_NAME3;
199 END;
200 Function C_ABS_TYPE_NAME4_p return varchar2 is
201 Begin
202 return C_ABS_TYPE_NAME4;
203 END;
204 Function C_ABS_TYPE_NAME5_p return varchar2 is
205 Begin
206 return C_ABS_TYPE_NAME5;
207 END;
208 Function C_ABS_TYPE_NAME6_p return varchar2 is
209 Begin
210 return C_ABS_TYPE_NAME6;
211 END;
212 Function C_ABS_TYPE_NAME7_p return varchar2 is
213 Begin
214 return C_ABS_TYPE_NAME7;
215 END;
216 Function C_ABS_TYPE_NAME8_p return varchar2 is
217 Begin
218 return C_ABS_TYPE_NAME8;
219 END;
220 Function C_ABS_TYPE_NAME9_p return varchar2 is
221 Begin
222 return C_ABS_TYPE_NAME9;
223 END;
224 Function C_ABS_TYPE_NAME10_p return varchar2 is
225 Begin
226 return C_ABS_TYPE_NAME10;
227 END;
228 Function C_PERSON_NAME_p return varchar2 is
229 Begin
230 return C_PERSON_NAME;
231 END;
232 Function C_NLS_LANGUAGE_p return varchar2 is
233 Begin
234 return C_NLS_LANGUAGE;
235 END;
236 END PER_PERRPRAA_XMLP_PKG ;