[Home] [Help]
PACKAGE BODY: APPS.PER_PERFRMMO_XMLP_PKG
Source
1 PACKAGE BODY PER_PERFRMMO_XMLP_PKG AS
2 /* $Header: PERFRMMOB.pls 120.1 2008/03/06 10:35:04 amakrish noship $ */
3
4 function CF_GET_HEADERFormula return Number is
5
6 begin
7
8
9 C_COUNT_PREVIOUS_PERIOD := perfrphr.get_emp_total(P_PERIOD_START_DATE - 1,
10 P_ESTABLISHMENT_ID,
11 NULL,
12 NULL,
13 'INCLUDE_MMO_HEADCOUNT',
14 P_INCLUDE_SUSPENDED);
15
16 C_COUNT_PERIOD := perfrphr.get_emp_total(P_PERIOD_END_DATE,
17 P_ESTABLISHMENT_ID,
18 NULL,
19 NULL,
20 'INCLUDE_MMO_HEADCOUNT',
21 P_INCLUDE_SUSPENDED);
22
23
24 C_COUNT_MEN := perfrphr.get_emp_total(P_PERIOD_END_DATE,
25 P_ESTABLISHMENT_ID,
26 NULL,
27 'M',
28 'INCLUDE_MMO_HEADCOUNT',
29 P_INCLUDE_SUSPENDED);
30
31 C_COUNT_WOMEN := perfrphr.get_emp_total(P_PERIOD_END_DATE,
32 P_ESTABLISHMENT_ID,
33 NULL,
34 'F',
35 'INCLUDE_MMO_HEADCOUNT',
36 P_INCLUDE_SUSPENDED);
37
38 C_TEMPORARY := perfrphr.get_emp_total(P_PERIOD_END_DATE,
39 P_ESTABLISHMENT_ID,
40 NULL,
41 NULL,
42 'INCLUDE_MMO_TEMPORARY',
43 P_INCLUDE_SUSPENDED);
44
45
46 --raise_application_error(-20001,C_COUNT_PREVIOUS_PERIOD||','||C_COUNT_PERIOD||','||C_COUNT_MEN||','||C_COUNT_WOMEN||','||C_TEMPORARY);
47 return(1);
48 end;
49
50 function BeforeReport return boolean is
51 begin
52 --hr_standard.event('BEFORE REPORT');
53 P_FORMULA_ID := hr_fr_mmo.Get_formula(P_BUSINESS_GROUP_ID,P_SESSION_DATE);
54 if (P_FORMULA_ID = 0) Then
55 return(FALSE);
56 else
57 return (TRUE);
58 end if;
59 end;
60
61 function c_get_nationalityformula(NATIONALITY in varchar2) return varchar2 is
62 lc_nationality varchar2(2);
63 begin
64
65 if (NATIONALITY IS NOT NULL) Then
66 begin
67 lc_nationality := hruserdt.get_table_value (P_BUSINESS_GROUP_ID,
68 'NATIONALITY',
69 'MMO_NATIONALITY',
70 NATIONALITY,
71 P_PERIOD_START_DATE);
72 if lc_nationality = 'FR' then
73 lc_nationality := 'F';
74 elsif lc_nationality = 'EU' then
75 lc_nationality := 'C';
76 else
77 lc_nationality := ' ';
78 end if;
79 exception when no_data_found then
80 lc_nationality := ' ';
81 end;
82 end if;
83 return lc_nationality;
84 end;
85
86 function c_get_asgformula(PERSON_ID1 in number, START_DATE in date, END_DATE in date) return number is
87
88 ln_start_asg_id number;
89 ln_end_asg_id number;
90 lc_start_job varchar2(240);
91 lc_start_job_pcs varchar2(150);
92 lc_end_job varchar2(240);
93 lc_end_job_pcs varchar2(150);
94 lc_start_reason varchar2(80);
95 lc_end_reason varchar2(80);
96 ld_effective_start_date date;
97
98 lid per_jobs.job_id%type;
99
100 begin
101 hr_utility.set_location('Entered C_GET_ASG formula', 5);
102 begin
103 hr_utility.set_location('Get asg_id,start job and start emp cat',10);
104
105 select a.assignment_id,
106 jbt.name,
107 j.job_id,
108 j.job_information1
109 into ln_start_asg_id,
110 lc_start_job,
111 lid,
112 lc_start_job_pcs
113 from per_all_assignments_f a,
114 per_jobs j,
115 per_jobs_tl jbt,
116 hr_soft_coding_keyflex s
117 where a.person_id = PERSON_ID1
118 and a.primary_flag = 'Y'
119 and a.effective_start_date = START_DATE
120 and a.assignment_type = 'E'
121 and a.job_id = jbt.job_id(+)
122 and jbt.language(+) = userenv('LANG')
123 and a.job_id = j.job_id(+)
124 and a.soft_coding_keyflex_id = s.soft_coding_keyflex_id(+);
125 IF lid is not null THEN
126 per_fr_d2_pkg.get_pcs_code(p_report_qualifier => 'MMO'
127 ,p_job_id => lid
128 ,p_pcs_code => lc_start_job_pcs
129 ,p_effective_date => START_DATE);
130 lid := null;
131 END IF;
132 EXCEPTION
133 WHEN OTHERS Then
134 Begin
135 ln_start_asg_id := '';
136 lc_start_job := '';
137 lc_start_job_pcs := '';
138 End;
139 end;
140
141 hr_utility.set_location('Get end_asg_id,end job and end emp cat',15);
142 begin
143 select a.assignment_id,
144 a.effective_start_date,
145 jbt.name,
146 j.job_id,
147 j.job_information1
148 into ln_end_asg_id,
149 ld_effective_start_date,
150 lc_end_job,
151 lid,
152 lc_end_job_pcs
153 from per_all_assignments_f a,
154 per_jobs j,
155 per_jobs_tl jbt,
156 hr_soft_coding_keyflex s
157 where a.person_id = PERSON_ID1
158 and a.primary_flag = 'Y'
159 and a.effective_end_date = END_DATE
160 and a.assignment_type = 'E'
161 and a.job_id = jbt.job_id(+)
162 and jbt.language(+) = userenv('LANG')
163 and a.job_id = j.job_id(+)
164 and a.soft_coding_keyflex_id = s.soft_coding_keyflex_id(+);
165 IF lid is not null THEN
166 per_fr_d2_pkg.get_pcs_code (p_report_qualifier => 'MMO'
167 ,p_job_id => lid
168 ,p_pcs_code => lc_end_job_pcs
169 ,p_effective_date => END_DATE);
170 lid := null;
171 END IF;
172 EXCEPTION
173 WHEN OTHERS Then
174 Begin
175 ln_end_asg_id := '';
176 lc_end_job := '';
177 lc_end_job_pcs := '';
178 end;
179 end;
180
181 if to_char(END_DATE,'YYYYMMDD') = '47121231' Then
182 C_JOB := lc_start_job;
183 C_JOB_PCS := lc_start_job_pcs;
184 else
185 C_JOB := lc_end_job;
186 C_JOB_PCS := lc_end_job_pcs;
187 end if;
188
189
190 if ln_start_asg_id is not NULL then
191 lc_start_reason := hr_fr_mmo.get_reason(ln_start_asg_id,
192 fnd_date.date_to_canonical(START_DATE),
193 P_FORMULA_ID,
194 'S');
195 if (lc_start_reason <> ' ') then
196 C_START_REASON := hruserdt.get_table_value (P_BUSINESS_GROUP_ID,
197 'FR_STARTING_REASON',
198 'MMO_STARTING_CATEGORY',
199 lc_start_reason,
200 START_DATE);
201 else
202 C_START_REASON := '';
203 end if;
204
205 end if;
206
207 if (ln_end_asg_id is not NULL) then
208 lc_end_reason := hr_fr_mmo.get_reason(ln_end_asg_id,
209 fnd_date.date_to_canonical(END_DATE),
210 P_FORMULA_ID,
211 'L');
212 if (lc_end_reason <> ' ') then
213 C_END_REASON := hruserdt.get_table_value (P_BUSINESS_GROUP_ID,
214 'FR_ENDING_REASON',
215 'MMO_ENDING_CATEGORY',
216 lc_end_reason,
217 END_DATE);
218 else
219 C_END_REASON := '';
220 end if;
221 end if;
222
223 if (START_DATE NOT BETWEEN P_PERIOD_START_DATE and
224 P_PERIOD_END_DATE)
225 or (START_DATE IS NULL) Then
226 C_CHECK_STARTED := 0;
227 else
228 C_CHECK_STARTED := 1;
229 end if;
230
231 if (END_DATE BETWEEN P_PERIOD_START_DATE and
232 P_PERIOD_END_DATE)
233 and (END_DATE IS NOT NULL) Then
234 C_CHECK_LEFT := 1;
235 else
236 C_CHECK_LEFT := 0;
237 end if;
238
239 return(1);
240
241 end;
242
243 function AfterReport return boolean is
244 begin
245 --hr_standard.event('AFTER REPORT');
246 return (TRUE);
247 end;
248
249 --Functions to refer Oracle report placeholders--
250
251 Function C_COUNT_PREVIOUS_PERIOD_p return number is
252 Begin
253 return C_COUNT_PREVIOUS_PERIOD;
254 END;
255 Function C_COUNT_PERIOD_p return number is
256 Begin
257 return C_COUNT_PERIOD;
258 END;
259 Function C_COUNT_MEN_p return number is
260 Begin
261 return C_COUNT_MEN;
262 END;
263 Function C_COUNT_WOMEN_p return number is
264 Begin
265 return C_COUNT_WOMEN;
266 END;
267 Function C_TEMPORARY_p return number is
268 Begin
269 return C_TEMPORARY;
270 END;
271 Function C_JOB_p return varchar2 is
272 Begin
273 return C_JOB;
274 END;
275 Function C_JOB_PCS_p return varchar2 is
276 Begin
277 return C_JOB_PCS;
278 END;
279 Function C_START_REASON_p return varchar2 is
280 Begin
281 return C_START_REASON;
282 END;
283 Function C_END_REASON_p return varchar2 is
284 Begin
285 return C_END_REASON;
286 END;
287 Function C_CHECK_STARTED_p return number is
288 Begin
289 return C_CHECK_STARTED;
290 END;
291 Function C_CHECK_LEFT_p return number is
292 Begin
293 return C_CHECK_LEFT;
294 END;
295 Function C_EFFECTIVE_START_DATE_p return date is
296 Begin
297 return C_EFFECTIVE_START_DATE;
298 END;
299 Function P_FORMULA_ID_p return number is
300 Begin
301 return P_FORMULA_ID;
302 END;
303 END PER_PERFRMMO_XMLP_PKG ;