[Home] [Help]
PACKAGE BODY: APPS.PER_FR_BIAF_REPORT
Source
1 PACKAGE BODY PER_FR_BIAF_REPORT AS
2 /* $Header: pefrbiaf.pkb 120.11 2006/09/20 20:08:01 aparkes noship $ */
3
4 level_cnt NUMBER;
5 vCtr NUMBER;
6
7
8 PROCEDURE fill_table (p_employee_number IN varchar2,p_bg_id IN NUMBER ,p_asg_id NUMBER,p_effective_date date) is
9
10 cursor c_get_data(lp_employee_number varchar2,lp_bg_id number,c_effective_date date) is
11 select distinct
12 apf.person_id,
13 paf.establishment_id est_id,
14 paf.contract_id,
15 apf.full_name ,
16 apf.date_of_birth,
17 apf.middle_names,
18 apf.per_information1 maiden,
19 apf.last_name ,
20 apf.first_name,
21 apf.national_identifier,
22 apf.original_date_of_hire,
23 hout.name establishment_name,
24 hla.address_line_1 Number_Road ,
25 hla.address_line_2 Complement ,
26 hla.address_line_3 Other ,
27 hla.region_2 INSEE_Code ,
28 hla.region_3 Small_Town ,
29 hla.postal_code Postal_Code ,
30 hla.town_or_city City ,
31 hla.region_1 Department ,
32 hla.country Country ,
33 hla.telephone_number_1 Telephone,
34 hla.telephone_number_2 Fax,
35 hla.telephone_number_3 Telephone3,
36 pav.address_line1 PNumber_Road,
37 pav.address_line2 PComplement,
38 pav.address_line3 POther,
39 pav.region_2 PINSEE_Code,
40 pav.region_3 PSmall_Town,
41 pav.postal_code PPostal_Code,
42 pav.town_or_city PCity,
43 pav.region_1 PDepartment,
44 pav.country PCountry,
45 pav.telephone_number_1 PTelephone,
46 pav.telephone_number_2 PFax,
47 pav.telephone_number_3 PTelephone3,
48 hoi.org_information2 siret,
49 hoi.org_information3 NAF,
50 hoi.org_information19 trg_bd_id,
51 pcf.ctr_information3 proposed_end_date,
52 pcf.ctr_information11 durationF,
53 pcf.ctr_information12 unitsF ,
54 pcf.duration duration,
55 pcf.duration_units units,
56 pcf.status status,
57 pcf.effective_start_date c_start_date,
58 pcf.effective_end_date c_end_date,
59 ppos.actual_termination_date actual_termination_date
60 from
61 hr_locations_all hla,
62 hr_all_organization_units hou,
63 hr_all_organization_units_tl hout,
64 per_all_assignments_f paf ,
65 per_all_people_f apf,
66 per_addresses pav,
67 hr_organization_information hoi,
68 per_contracts_f pcf,
69 hr_soft_coding_keyflex hsck,
70 per_periods_of_service ppos
71 where hou.organization_id= paf.establishment_id
72 and hout.organization_id=hou.organization_id
73 and hout.language=userenv('lang')
74 and hla.location_id(+)=hou.location_id
75 and hoi.organization_id(+)=paf.establishment_id
76 and hoi.org_information_context(+) ='FR_ESTAB_INFO'
77 and hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id
78 and hsck.segment2 <> 'STUDENT'
79 and ppos.person_id=paf.person_id
80 and ppos.period_of_service_id=paf.period_of_service_id
81 and ppos.actual_termination_date is not null
82 and paf.person_id=apf.person_id
83 and paf.contract_id is not null
87 and pcf.type not in ('APPRENTICESHIP','ORIENTATION','ADAPTATION','QUALIFICATION')
84 and paf.contract_id = pcf.contract_id
85 and paf.person_id=pcf.person_id
86 and pcf.ctr_information2 like 'FIXED_TERM'
88 and pav.person_id(+)=apf.person_id
89 and pav.primary_flag(+)='Y'
90 and pav.business_group_id(+)=apf.business_group_id
91 and apf.employee_number=lp_employee_number
92 and apf.business_group_id=lp_bg_id
93 and c_effective_date between apf.effective_start_date and apf.effective_end_date
94 and c_effective_date between pcf.effective_start_date and pcf.effective_end_date
95 and c_effective_date >= paf.effective_end_date
96 and paf.effective_end_date=ppos.actual_termination_date;
97
98
99
100 cursor c_trg_addr(l_tr_bd_id number, l_bg_id number)is
101 select hrvt.name trg_bd_name,
102 hla.address_line_1 Number_Road ,
103 hla.address_line_2 Complement ,
104 hla.address_line_3 Other ,
105 hla.region_2 INSEE_Code ,
106 hla.region_3 Small_Town ,
107 hla.postal_code Postal_Code ,
108 hla.town_or_city City ,
109 hla.region_1 Department ,
110 hla.country Country ,
111 hla.telephone_number_1 Telephone,
112 hla.telephone_number_2 Fax,
113 hla.telephone_number_3 Telephone3
114 from hr_organization_information hoi,
115 hr_all_organization_units hrv,
116 hr_all_organization_units_tl hrvt,
117 hr_locations_all hla
118 where hoi.ORG_INFORMATION_CONTEXT='CLASS'
119 and hoi.org_information1='FR_OPAC'
120 and hrv.business_group_id=l_bg_id
121 and hrv.organization_id=hoi.organization_id
122 and hrv.organization_id=l_tr_bd_id
123 and hla.location_id(+)=hrv.location_id
124 and hrvt.organization_id=hrv.organization_id
125 and hrvt.language=userenv('lang');
126
127 cursor c_get_lookup is
128 select lookup_code,meaning from hr_lookups
129 where lookup_type='BIAF_LOOKUP_CODE';
130
131 lc_trg_addr c_trg_addr%ROWTYPE;
132 l_naf_meaning varchar2(40);
133 begin
134
135 ------hr_utility.trace('Into fill_table get_data emp no '||p_employee_number||' BG ID '||to_char(p_bg_id)||' Date '||to_char(p_effective_date));
136
137 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'report_date';
138 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := to_char(sysdate,'YYYY-MM-DD');
139 vCtr:=vCtr+1;
140
141 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'effective_date';
142 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := to_char(p_effective_date
143 ,'YYYY-MM-DD');
144 vCtr:=vCtr+1;
145
146 for l_cursor_get_data in c_get_data(p_employee_number,p_bg_id ,p_effective_date)----For each person Body of Template
147 loop
148
149 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'L_REPORT';
150 vCtr:=vCtr+1;
151
152 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'FLAG';
153 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := '1';
154 vCtr:=vCtr+1;
155 ------hr_utility.trace('Into cursor '||p_employee_number);
156 for l_c_get_lookup in c_get_lookup ----From Lookup BIAF_LOOKUP_CODE --
157 loop
158
159
160 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := l_c_get_lookup.lookup_code;
161 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_c_get_lookup.meaning);
162 vCtr:=vCtr+1;
163
164
165 end loop; ---Label of the template
166
167
168 ------------hr_utility.trace('Into fill_table get_data emp no '||(p_employee_number));
169 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'employee_number';
170 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := p_employee_number;
171 vCtr:=vCtr+1;
172
173 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'last_name';
174 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.last_name);
175 vCtr:=vCtr+1;
176
177 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'first_name';
178 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.first_name);
179 vCtr:=vCtr+1;
180
181 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'full_name';
182 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.full_name);
183 vCtr:=vCtr+1;
184
185 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'maiden';
186 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.maiden);
187 vCtr:=vCtr+1;
188
189 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'date_of_birth';
190 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := to_char(l_cursor_get_data.date_of_birth,'YYYY-MM-DD');
191 vCtr:=vCtr+1;
192
193 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'national_identifier';
194 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.national_identifier);
195 vCtr:=vCtr+1;
196
197 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'original_date_of_hire';
198 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := to_char(get_contract_start_date(l_cursor_get_data.person_id),'YYYY-MM-DD');
199 vCtr:=vCtr+1;
200
201 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'contract_start_date';
202 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := to_char(hr_contract_api.get_active_start_date(l_cursor_get_data.contract_id,p_effective_date,l_cursor_get_data.status),'YYYY-MM-DD');
203 vCtr:=vCtr+1;
204
205 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'contract_end_date';
206 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := to_char(hr_contract_api.get_active_end_date(l_cursor_get_data.contract_id,p_effective_date,l_cursor_get_data.status),'YYYY-MM-DD');
207 vCtr:=vCtr+1;
208
209 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'proposed_end_date';
213 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'duration';
210 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := to_char(fnd_date.canonical_to_date(l_cursor_get_data.proposed_end_date),'YYYY-MM-DD');
211 vCtr:=vCtr+1;
212
214 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := to_char(l_cursor_get_data.duration);
215 vCtr:=vCtr+1;
216
217 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'unit';
218 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (hr_contract_api.get_meaning(l_cursor_get_data.units,'QUALIFYING_UNITS'));
219 vCtr:=vCtr+1;
220
221
222 -----Person Address Section-------------
223
224
225
226 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP1';
227 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.PNumber_Road);
228 vCtr:=vCtr+1;
229
230 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP2';
231 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.PComplement);
232 vCtr:=vCtr+1;
233
234 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP3';
235 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.POther);
236 vCtr:=vCtr+1;
237
238 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP4';
239 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.PINSEE_Code);
240 vCtr:=vCtr+1;
241
242 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP5';
243 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.PSmall_Town);
244 vCtr:=vCtr+1;
245
246 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP6';
247 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.PPostal_Code);
248 vCtr:=vCtr+1;
249
250 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP7';
251 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.PCity);
252 vCtr:=vCtr+1;
253
254 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP8';
255 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.PDepartment);
256 vCtr:=vCtr+1;
257
258 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP9';
259 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.PCountry);
260 vCtr:=vCtr+1;
261
262 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP10';
263 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.PTelephone);
264 vCtr:=vCtr+1;
265
266 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP11';
267 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue :=(l_cursor_get_data.PFax);
268 vCtr:=vCtr+1;
269
270 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADP12';
271 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.PTelephone3);
272 vCtr:=vCtr+1;
273
274 -----------END OF PERSONAL ADDRESS--------------------------------------------------------
275
276
277
278 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'siret';
279 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.siret);
280 vCtr:=vCtr+1;
281
282 select meaning into l_naf_meaning from fnd_common_lookups where lookup_type='FR_NAF_CODE'
283 and lookup_code=l_cursor_get_data.NAF;
284
285 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ape';
286 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_naf_meaning);
287 vCtr:=vCtr+1;
288
289
290
291
292 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'establishment_headcount';
293 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue :=to_char( get_emp_total(lp_effective_date =>l_cursor_get_data.actual_termination_date, --get_contract_start_date(l_cursor_get_data.person_id),
294 lp_est_id =>l_cursor_get_data.est_id ,
295 -- lp_ent_id =>null,
296 -- lp_sex =>null,
297 lp_udt_column => 'INCLUDE_DUE'
298 -- lp_include_suspended =>'Y'
299 ));
300 vCtr:=vCtr+1;
301 hr_utility.trace('Establishment Headcount ');
302
303 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'establishment_name';
304 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.establishment_name);
305 vCtr:=vCtr+1;
306
307 ------ESTABLISHMENT ADDRESS SECTION---------------------------------------
308
309 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE1';
310 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.Number_Road);
311 vCtr:=vCtr+1;
312
313 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE2';
314 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.Complement);
315 vCtr:=vCtr+1;
316
317 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE3';
318 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.Other);
319 vCtr:=vCtr+1;
320
321 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE4';
322 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.INSEE_Code);
323 vCtr:=vCtr+1;
324
325 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE5';
326 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.Small_Town);
327 vCtr:=vCtr+1;
328
329 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE6';
330 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.Postal_Code);
331 vCtr:=vCtr+1;
332
333 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE7';
334 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.City);
335 vCtr:=vCtr+1;
336
340
337 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE8';
338 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.Department);
339 vCtr:=vCtr+1;
341 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE9';
342 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.Country);
343 vCtr:=vCtr+1;
344
345 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE10';
346 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.Telephone);
347 vCtr:=vCtr+1;
348
349 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE11';
350 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue :=(l_cursor_get_data.Fax);
351 vCtr:=vCtr+1;
352
353 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADE12';
354 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_cursor_get_data.Telephone3);
355 vCtr:=vCtr+1;
356
357 -----------------------END OF ESTABLISHMENT ADDRESS--------------------
358
359 /*
360 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'establishment_address';
361 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (l_establishment_address);
362 vCtr:=vCtr+1; */
363
364 -----------------------TRAINING BODY ADDRESS SECTION---------------------
365
366 open c_trg_addr(l_cursor_get_data.trg_bd_id, p_bg_id);
367 fetch c_trg_addr into lc_trg_addr;
368
369 hr_utility.trace('Training Address ');
370
371 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'tax_paid_to';
372 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.trg_bd_name);
373 vCtr:=vCtr+1;
374
375
376 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT1';
377 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.Number_Road);
378 vCtr:=vCtr+1;
379
380 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT2';
381 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.Complement);
382 vCtr:=vCtr+1;
383
384 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT3';
385 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.Other);
386 vCtr:=vCtr+1;
387
388 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT4';
389 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.INSEE_Code);
390 vCtr:=vCtr+1;
391
392 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT5';
393 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.Small_Town);
394 vCtr:=vCtr+1;
395
396 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT6';
397 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.Postal_Code);
398 vCtr:=vCtr+1;
399
400 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT7';
401 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.City);
402 vCtr:=vCtr+1;
403
404 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT8';
405 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.Department);
406 vCtr:=vCtr+1;
407
408 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT9';
409 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.Country);
410 vCtr:=vCtr+1;
411
412 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT10';
413 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.Telephone);
414 vCtr:=vCtr+1;
415
416 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT11';
417 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue :=(lc_trg_addr.Fax);
418 vCtr:=vCtr+1;
419
420 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'ADT12';
421 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagValue := (lc_trg_addr.Telephone3);
422 vCtr:=vCtr+1;
423 close c_trg_addr; --Cursor for each person Traning Body Address
424
425 PER_FR_BIAF_REPORT.vXMLTable(vCtr).TagName := 'L_REPORT';
426 vCtr:=vCtr+1;
427
428 end loop;----For each Person
429 end fill_table;
430
431
432 FUNCTION get_emp_total (lp_effective_date IN DATE,
433 lp_est_id IN NUMBER ,
434 --lp_ent_id IN NUMBER ,
435 -- lp_sex IN VARCHAR2,
436 lp_udt_column IN VARCHAR2
437 -- lp_include_suspended IN VARCHAR2
438 ) RETURN NUMBER IS
439 --
440 CURSOR c_get_total(p_effective_date IN DATE ,
441 p_est_id IN NUMBER ,
442 -- p_ent_id IN NUMBER ,
443 -- p_sex IN VARCHAR2 ,
444 p_udt_column IN VARCHAR2
445 --p_include_suspended IN VARCHAR2
446 ) IS
447 SELECT COUNT(asg.assignment_id)
448 FROM per_all_assignments_f asg,
449 per_assignment_status_types ast,
450 -- per_person_types_v pt,
451 per_all_people_f peo
452 WHERE asg.establishment_id=p_est_id
453 AND asg.person_id = peo.person_id
454 AND (ast.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN')) -- AND p_include_suspended = 'Y')
455 AND asg.assignment_status_type_id = ast.assignment_status_type_id
456 AND asg.primary_flag = 'Y'
457 and exists ( select null
458 from per_person_type_usages_f pf,
459 per_person_types pt
460 where pf.person_id=peo.person_id
461 and pf.person_type_id = pt.person_type_id
462 --and pt.language=userenv('lang')
463 and 'Y' = pefrusdt.get_table_value(peo.business_group_id
464 ,'FR_USER_PERSON_TYPE'
465 ,p_udt_column
469 )
466 ,pt.user_person_type
467 ,p_effective_date)
468 and p_effective_date between pf.effective_start_date and pf.effective_end_date
470 /*AND peo.person_type_id = pt.person_type_id
471 AND 'Y' = pefrusdt.get_table_value(peo.business_group_id
472 ,'FR_USER_PERSON_TYPE'
473 ,p_udt_column
474 ,pt.user_person_type
475 ,p_effective_date)*/
476 AND p_effective_date >= asg.effective_start_date
477 AND p_effective_date <= asg.effective_end_date
478 AND p_effective_date >= peo.effective_start_date
479 AND p_effective_date <= peo.effective_end_date;
480 /*
481 AND (LEAST(asg.effective_end_date,peo.effective_end_date) > p_effective_date
482 OR EXISTS (SELECT null
483 FROM per_all_assignments_f asg2,
484 per_assignment_status_types ast2,
485 -- per_person_types_v pt2,
486 per_all_people_f peo2
487 WHERE asg2.establishment_id =p_est_id
488 AND asg2.person_id = peo.person_id
489 AND asg2.person_id = peo2.person_id
490 AND (ast2.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN') AND p_include_suspended = 'Y')
491 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
492 AND asg2.primary_flag = 'Y'
493 and exists ( select null
494 from per_person_type_usages_f pf2, per_person_types pt2
495 where pf2.person_id=peo2.person_id
496 and pf2.person_type_id = pt2.person_type_id
497 and 'Y' = pefrusdt.get_table_value(peo2.business_group_id
498 ,'FR_USER_PERSON_TYPE'
499 ,p_udt_column
500 ,pt2.user_person_type
501 ,p_effective_date)
502 )
503 /*AND peo2.person_type_id = pt2.person_type_id
504 AND 'Y' = pefrusdt.get_table_value(peo2.business_group_id
505 ,'FR_USER_PERSON_TYPE'
506 ,p_udt_column
507 ,pt2.user_person_type
508 ,p_effective_date)
509 AND p_effective_date+1 >= asg2.effective_start_date
510 AND p_effective_date+1 <= asg2.effective_end_date
511 AND p_effective_date+1 >= peo2.effective_start_date
512 AND p_effective_date+1 <= peo2.effective_end_date)
513 ); */
514 --
515 l_total NUMBER:=0;
516
517 --
518 BEGIN
519 --
520 OPEN c_get_total(lp_effective_date,lp_est_id,lp_udt_column);
521 FETCH c_get_total INTO l_total;
522 CLOSE c_get_total;
523 --
524 RETURN l_total;
525 --
526 EXCEPTION
527 WHEN NO_DATA_FOUND THEN
528 CLOSE c_get_total;
529 RETURN(0);
530 end get_emp_total;
531
532
533
534
535 FUNCTION get_contract_start_date(f_person_id IN number) return date
536 is
537 cursor get_date(f_person_id number) is
538 select pcf.effective_start_date,
539 pcf.effective_end_date
540 from per_contracts_f pcf ,
541 per_all_assignments_f paf,
542 per_all_people_f ppf
543 where ppf.person_id=f_person_id
544 and paf.person_id=ppf.person_id
545 and paf.contract_id(+)=pcf.contract_id
546 and pcf.effective_start_date=(select min(pcf1.effective_start_date)
547 from per_contracts_f pcf1
548 where pcf1.contract_id=pcf.contract_id);
549
550 l_start_date date;
551 l_end_date date;
552 begin
553
554 open get_date(f_person_id);
555 fetch get_date into l_start_date,l_end_date;
556 close get_date;
557
558 return l_start_date;
559 end get_contract_start_date;
560
561
562
563
564 FUNCTION get_contract_end_date(f_person_id IN number) return date
565 is
566 cursor get_date(f_person_id number) is
567 select active_end_date
568 from per_contracts
569 where person_id=f_person_id
570 and active_start_date=( select max(active_end_date) from
571 per_contracts where person_id=f_person_id);
572
573 l_end_date date;
574 begin
575
576 open get_date(f_person_id);
577 fetch get_date into l_end_date;
578 close get_date;
579
580 return l_end_date;
581 end get_contract_end_date;
582
583
584
585 PROCEDURE POPULATE_REPORT_DATA(p_employee_number IN varchar2,p_bg_id IN NUMBER ,p_asg_id NUMBER,p_asg_emp varchar2 ,p_effective_date varchar2 ,p_xfdf_blob OUT NOCOPY BLOB) IS
586
587 /* cursor c_get_data(lp_person_id number) is
588 select apf.full_name ,apf.last_name ,apf.first_name, apf.national_identifier,apf.original_date_of_hire from
589 per_all_people_f apf where apf.person_id=lp_person_id;*/
590
594 hr_assignment_sets hs,
591 cursor c_asg_set1(l_asg_id number,l_bg_id number, p_effective_date date) is
592 select distinct paf.person_id ,l_asg_id ,pef.employee_number
593 from per_all_assignments_f paf,
595 per_all_people_f pef
596 where hs.assignment_set_id=l_asg_id
597 and nvl(hs.payroll_id,paf.payroll_id)=paf.payroll_id
598 and hs.business_group_id=paf.business_group_id
599 and pef.person_id=paf.person_id
600 and paf.business_group_id=l_bg_id
601 and paf.assignment_id in ( select assignment_id
602 from hr_assignment_set_amendments hsa
603 where hsa.assignment_set_id =l_asg_id
604 and hsa.include_or_exclude='I')
605 and p_effective_date between pef.effective_start_date and pef.effective_end_date ;
606
607
608 cursor c_asg_set2(l_asg_id number,l_bg_id number, p_effective_date date) is
609 select distinct paf.person_id ,l_asg_id ,pef.employee_number
610 from per_all_assignments_f paf,
611 hr_assignment_sets hs,
612 per_all_people_f pef
613 where hs.assignment_set_id=l_asg_id
614 and hs.business_group_id=paf.business_group_id
615 and nvl(hs.payroll_id,paf.payroll_id)=paf.payroll_id
616 and pef.person_id=paf.person_id
617 and paf.business_group_id=l_bg_id
618 and paf.assignment_id not in ( select assignment_id
619 from hr_assignment_set_amendments hsa
620 where hsa.assignment_set_id =l_asg_id
621 and hsa.include_or_exclude='E')
622 and p_effective_date between pef.effective_start_date and pef.effective_end_date ;
623
624 cursor c_asg_amendments is
625 select /*count(hsa.include_or_exclude) cnt , */ hsa.include_or_exclude ioe
626 from hr_assignment_set_amendments hsa
627 where hsa.assignment_set_id =p_asg_id;
628 -- group by hsa.include_or_exclude;
629
630 /*
631 UNION
632 select paf.person_id ,l_asg_id ,pef.employee_number
633 from per_all_assignments_f paf,hr_assignment_sets hs,per_all_people_f pef
634 where hs.assignment_set_id=l_asg_id
635 and hs.payroll_id is null
636 and hs.business_group_id=paf.business_group_id
637 and pef.person_id=paf.person_id
638 and paf.business_group_id= l_bg_id
639 and paf.assignment_id not in ( select assignment_id from hr_assignment_set_amendments hsa
640 where hsa.assignment_set_id =hs.assignment_set_id
641 and hsa.include_or_exclude='E'
642 )
643 and p_effective_date between pef.effective_start_date and pef.effective_end_date
644 and p_effective_date between paf.effective_start_date and paf.effective_end_date ;
645 */
646 l_c_asg_amendments c_asg_amendments%ROWTYPE ;
647 l_establishment_address varchar2(1000);
648 l_person_address varchar2(1000);
649 l_effective_date date;
650 l_ioex varchar2(1);
651 l_count number;
652 begin
653
654 l_effective_date:=trunc(fnd_date.canonical_to_date(p_effective_date));
655
656
657
658 --hr_utility.trace_on(null,'BIAF1');
659 PER_FR_BIAF_REPORT.vXMLTable.DELETE;
660 vCtr:=0;
661
662 hr_utility.TRACE('Effective Date ' || to_char(l_effective_date));
663 ------hr_utility.TRACE('Business Group ID ' || p_bg_id);
664 ------hr_utility.TRACE('Assignment Or Employee ' || p_asg_emp);
665
666
667 if (p_asg_emp='A') then
668
669 open c_asg_amendments;
670 fetch c_asg_amendments into l_c_asg_amendments;
671
672
673 if (c_asg_amendments%FOUND) then
674
675 if (l_c_asg_amendments.ioe='I' /*and l_c_asg_amendments.cnt > 0*/) then
676 for l_c_asg_set in c_asg_set1(p_asg_id,p_bg_id,l_effective_date) ----Assignment Set Loop
677 loop
678 ------hr_utility.TRACE('Inside assignment Set ' );
679
680 hr_utility.TRACE('Employee Number ' || to_char(l_c_asg_set.employee_number));
681 fill_table(l_c_asg_set.employee_number,p_bg_id,p_asg_id,l_effective_date);
682
683 end loop ; ----For Assignment Set
684 end if;
685
686 if (l_c_asg_amendments.ioe='E' /* and l_c_asg_amendments.cnt > 0*/) then
687
688 for l_c_asg_set in c_asg_set2(p_asg_id,p_bg_id,l_effective_date) ----Assignment Set Loop
689 loop
690 ------hr_utility.TRACE('Inside assignment Set ' );
691
692 hr_utility.TRACE('Employee Number2 ' || to_char(l_c_asg_set.employee_number));
693 fill_table(l_c_asg_set.employee_number,p_bg_id,p_asg_id,l_effective_date);
694
695 end loop ; ----For Assignment Set
696 end if;
697
698
699 else ---- No amendment is mentioned
700 for l_c_asg_set in c_asg_set2(p_asg_id,p_bg_id,l_effective_date) ----Assignment Set Loop
701 loop
702 ------hr_utility.TRACE('Inside assignment Set ' );
703
704 hr_utility.TRACE('Employee Number3 ' || to_char(l_c_asg_set.employee_number));
705 fill_table(l_c_asg_set.employee_number,p_bg_id,p_asg_id,l_effective_date);
706
707 end loop ; ----For Assignment Set
708 end if;
709
710 close c_asg_amendments;
711 else
712 hr_utility.TRACE('Employee Number4 ' || to_char(p_employee_number));
713 fill_table(p_employee_number,p_bg_id,p_asg_id,l_effective_date);
714
715 end if;
716
717 --hr_utility.TRACE('Counter ' || (vCtr));
718
719 PER_FR_BIAF_REPORT.WritetoCLOB (p_xfdf_blob );
720
721
722
723 end POPULATE_REPORT_DATA;
724
725
726
727
728 PROCEDURE WritetoCLOB (p_xfdf_blob out nocopy blob) IS
729
730 l_xfdf_string clob;
731 l_str1 varchar2(1000);
732 l_str2 varchar2(20);
733 l_str3 varchar2(20);
734 l_str4 varchar2(20);
735 l_str5 varchar2(20);
736 l_str6 varchar2(30);
737 l_str7 varchar2(1000);
738 l_str8 varchar2(1000);
739 l_str9 varchar2(1000);
740 l_boo number :=1;
741 begin
742 ----------hr_utility.set_location('Entered Procedure Write to clob ',100);
743 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
744 <FIELDS> ';
745 -- <fields> ' ;
746 l_str2 := '<';
747 l_str3 := '>';
748 -- l_str4 := '</xfdf>' ;
749 l_str5 := '</' ;
750 l_str6 := '</FIELDS> ';
751 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
752 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
753 </xfdf>';
754 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
755 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
756 ----hr_utility.TRACE('TAble count :'||(vXMLTable.count));
757 if vXMLTable.count > 2 then
758 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
759 --hr_utility.trace(l_str1);
760 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
761 l_str8 := vXMLTable(ctr_table).TagName;
762 l_str9 := vXMLTable(ctr_table).TagValue;
763
764 IF (l_str8='L_REPORT') THEN
765
766 IF (l_boo=1) THEN
767 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );--- <
768 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);------ name
769 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );---->
770 --hr_utility.trace(l_str2||l_str8||l_str3);
771 ELSE
772 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );---- </
773 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);----- name
774 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );----- >
775 --hr_utility.trace(l_str5||l_str8||l_str3);
776 END IF;
777 l_boo:=l_boo*(-1);
778 END IF;
779
780
781 if ((l_str9 is not null)and (l_str8 not like 'L_REPORT')) then
782 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );--- <
783 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);------ name
784 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );---->
785 --dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
786 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);-----value
787 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );---- </
788 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);----- name
789 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );----- >
790 --hr_utility.trace(l_str2||l_str8||l_str3||l_str9||l_str5||l_str8||l_str3);
791 else
792 null;
793 end if;
794
795 END LOOP;
796 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
797 --hr_utility.trace(l_str6);
798 else
799 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
800 --hr_utility.trace(l_str7);
801 end if;
802 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
803 clob_to_blob(l_xfdf_string,p_xfdf_blob);
804 ----hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
805 --return p_xfdf_blob;
806 EXCEPTION
807 WHEN OTHERS then
808 ----hr_utility.TRACE('sqleerm ' || SQLERRM);
809 ----hr_utility.RAISE_ERROR;
810 null;
811 END WritetoCLOB;
812
813
814
815 Procedure clob_to_blob(p_clob clob,
816 p_blob IN OUT NOCOPY Blob)
817 is
818 l_length_clob number;
819 l_offset pls_integer;
820 l_varchar_buffer varchar2(32767);
821 l_raw_buffer raw(32767);
822 l_buffer_len number:= /*32000*/ 20000;
823 l_chunk_len number;
824 l_blob blob;
825 g_nls_db_char varchar2(60);
826
827 l_raw_buffer_len pls_integer;
828 l_blob_offset pls_integer := 1;
829
830 begin
831 hr_utility.set_location('Entered Procedure clob to blob',120);
832 select userenv('LANGUAGE') into g_nls_db_char from dual;
833 l_length_clob := dbms_lob.getlength(p_clob);
834 l_offset := 1;
835 while l_length_clob > 0 loop
836 hr_utility.trace('l_length_clob '|| l_length_clob);
837 if l_length_clob < l_buffer_len then
838 l_chunk_len := l_length_clob;
839 else
840 l_chunk_len := l_buffer_len;
841 end if;
842 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
843 -- fnd_file.put_line(fnd_file.log,l_varchar_buffer);
844 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.AL32UTF8',g_nls_db_char);
845
846 l_raw_buffer_len := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.AL32UTF8',g_nls_db_char));
847
848
849 -- dbms_lob.write(p_blob,l_chunk_len, l_offset, l_raw_buffer);
850 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
851 --fnd_file.put_line(fnd_file.log,l_varchar_buffer);
852 l_blob_offset := l_blob_offset + l_raw_buffer_len;
853 l_offset := l_offset + l_chunk_len;
854 l_length_clob := l_length_clob - l_chunk_len;
855 hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
856 end loop;
857 hr_utility.set_location('Finished Procedure clob to blob ',130);
858 end;
859
860
861 end PER_FR_BIAF_REPORT;
862
863