[Home] [Help]
PACKAGE BODY: APPS.PER_ZA_WSP_XML_GEN_PKG
Source
1 package body PER_ZA_WSP_XML_GEN_PKG as
2 /* $Header: perzawspg.pkb 120.0.12000000.2 2007/06/20 11:53:57 amahanty noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 2001 Oracle Corporation Redwood Shores, California, USA|
6 | All rights reserved. |
7 +======================================================================+
8 Package Name : PER_ZA_WSP_XML_GEN_PKG
9 Package File Name : perzawspg.pkb
10 Description : This sql script seeds the Package Body that
11 generates XML data for WSP report.
12
13 Change List : perzaxmlg.pkb
14 ------------
15 Name Date Version Bug Text
16 ------------- ------------- ------- ------- ------------------------------
17 Kaladhaur 27-DEC-2006 115.0 First created
18 R V Pahune 05-Feb-2007 115.0 Changed the action context type
19 to AAP for person details.
20 A. Mahanty 19-Feb-2007 115.0 Changed the race codes
21 A. Mahanty 20-Feb-2007 115.1 A3 part of the report shows
22 one entry per person and not
23 by learning intervention.
24 A. Mahanty 25-Feb-2007 115.2 Added Totals calculation for WSP
25 and ATR (A3 and B3). Added date
26 fields to the report
27 A. Mahanty 06-Mar-2007 115.3 p_xml was opened explicitly in
28 populate_xml_data
29 ========================================================================*/
30 --
31 -- Global Variables
32 --
33 g_package constant varchar2(31) := 'PER_ZA_WSP_XML_GEN_PKG.';
34 g_debug boolean;
35 --
36 type xml_dom_table is table of xmldom.DOMNode index by binary_integer;
37 --
38 g_xml_dom xmldom.DOMDocument;
39 g_node_list xml_dom_table;
40 --
41 g_root_level constant binary_integer := 0;
42 g_company_level constant binary_integer := 1;
43 g_company_name_level constant binary_integer := 2;
44 g_company_name_lin_level constant binary_integer := 3;
45 g_company_add_level constant binary_integer := 2; -- for both physical and postal
46 g_company_add_det_level constant binary_integer := 3; -- for both physical and postal
47 g_company_sdf_level constant binary_integer := 2;
48 g_company_sdf_add_level constant binary_integer := 3;
49 g_company_bank_level constant binary_integer := 2;
50 g_company_bank_add_level constant binary_integer := 3;
51 g_company_det_level constant binary_integer := 2;
52
53 g_wsp_level constant binary_integer := 1;
54 g_wsp_date_level constant binary_integer := 2;
55 g_wsp_trng_level constant binary_integer := 2;
56 g_wsp_trng_det_level constant binary_integer := 3;
57 g_wsp_benf_level constant binary_integer := 2;
58 g_wsp_benf_det_level constant binary_integer := 3;
59
60 g_atr_level constant binary_integer := 1;
61 g_atr_trng_level constant binary_integer := 2;
62 g_atr_trng_det_level constant binary_integer := 3;
63 g_atr_benf_level constant binary_integer := 2;
64 g_atr_benf_det_level constant binary_integer := 3;
65
66 --
67 /*--------------------------------------------------------------------------
68 Name : preprocess_value
69 Purpose : Called from PY_ZA_SRS_WSP_PREID valueset
70 Arguments :
71 --------------------------------------------------------------------------*/
72 function preprocess_value
73 (
74 p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
75 p_legislative_parameters in pay_payroll_actions.legislative_parameters%type
76 )
77 return varchar2 is
78
79 l_preprocess_value varchar2(80);
80
81 begin
82
83 l_preprocess_value := 'Plan Year=';
84 l_preprocess_value := l_preprocess_value || pay_za_uif_archive_pkg.get_parameter('PLAN_YEAR', p_legislative_parameters);
85 l_preprocess_value := l_preprocess_value ||'('||to_char(p_payroll_action_id)||')';
86
87 return l_preprocess_value;
88
89 end preprocess_value;
90
91 --
92 /*--------------------------------------------------------------------------
93 Name : update_dom
94 Purpose : Update a node to XML DOM.
95 Arguments :
96 --------------------------------------------------------------------------*/
97 procedure update_dom( p_name varchar2
98 , p_text varchar2
99 , p_level binary_integer) is
100
101 parent_node xmldom.DOMNode;
102
103 item_node xmldom.DOMNode;
104 item_elmt xmldom.DOMElement;
105 item_text xmldom.DOMText;
106
107 begin
108 parent_node := g_node_list(p_level);
109
110 item_elmt := xmldom.createElement(g_xml_dom, p_name);
111 item_node := xmldom.appendChild(parent_node, xmldom.makeNode(item_elmt));
112
113 if p_text is not null
114 then
115 item_text := xmldom.createTextNode(g_xml_dom, p_text);
116 item_node := xmldom.appendChild(item_node, xmldom.makeNode(item_text));
117 end if;
118
119 g_node_list(p_level+1) := item_node;
120 end update_dom;
121 --
122 /*--------------------------------------------------------------------------
123 Name : update_company_data
124 Purpose : Updates employer data to XML DOM.
125 Assumes g_xml_dom is already initialized.
126 Arguments :
127 --------------------------------------------------------------------------*/
128 procedure update_company_data( p_legal_entity_id in number
129 , p_payroll_action_id in varchar2) is
130
131 l_proc varchar2(100) := g_package || 'update_company_data';
132
133 cursor csr_comp_contacts is
134 select action_information3 org_name
135 , action_information4 post_add_line_1 -- A(1).2 Postal Address
136 , action_information5 post_add_line_2
137 , action_information6 post_add_line_3
138 , action_information8 post_town_or_city
139 , action_information9 post_postal_code
140 , action_information10 post_province
141 , action_information11 phy_add_line_1 -- A(1).3 Physical Address
142 , action_information12 phy_add_line_2
143 , action_information13 phy_add_line_3
144 , action_information15 phy_town_or_city
145 , action_information16 phy_postal_code
146 , action_information17 phy_province
147 , action_information18 tel_no -- A(1).5 Telephone number
148 , action_information19 fax_no -- A(1).6 Fax number
149 , action_information20 email_add -- A(1).7 E-mail Address
150 from pay_action_information
151 where action_context_id = p_payroll_action_id
152 and action_information_category = 'ZA WSP EMPLOYER DETAILS'
153 and action_information2 = p_legal_entity_id
154 and action_context_type = 'PA';
155
156 comp_contacts_rec csr_comp_contacts%rowtype;
157
158 cursor csr_comp_sdf_det is
159 select action_information3 sdf_name -- A(1).12 SDF Name
160 , action_information4 sdf_add_line_1 -- A(1).13 SDF Address
161 , action_information5 sdf_add_line_2
162 , action_information6 sdf_add_line_3
163 , action_information7 sdf_town_or_city
164 , action_information8 sdf_postal_code
165 , action_information9 sdf_province
166 , action_information10 sdf_tel -- A(1).14 SDF contact details
167 , action_information11 sdf_mobile
168 , action_information12 sdf_fax
169 , action_information13 sdf_email
170 from pay_action_information
171 where action_context_id = p_payroll_action_id
172 and action_information_category = 'ZA WSP SDF DETAILS'
173 and action_information2 = p_legal_entity_id
174 and action_context_type = 'PA';
175
176 comp_sdf_det_rec csr_comp_sdf_det%rowtype;
177
178 begin
179 hr_utility.set_location ('Entering ' || l_proc, 10);
180
181 -- Update EMPLOYER DETAILS
182 open csr_comp_contacts;
183 fetch csr_comp_contacts into comp_contacts_rec;
184 close csr_comp_contacts;
185
186 update_dom('COMPANY', null, g_company_level);
187
188 update_dom('NAME', null, g_company_name_level);
189 update_dom('LINE_1', comp_contacts_rec.org_name, g_company_name_lin_level);
190
191 update_dom('POSTAL_ADDRESS', null, g_company_add_level);
192 update_dom('ADDRESS_LINE_1', comp_contacts_rec.post_add_line_1 , g_company_add_det_level);
193 update_dom('ADDRESS_LINE_2', comp_contacts_rec.post_add_line_2 , g_company_add_det_level);
194 update_dom('ADDRESS_LINE_3', comp_contacts_rec.post_add_line_3 , g_company_add_det_level);
195 update_dom('TOWN_CITY' , comp_contacts_rec.post_town_or_city, g_company_add_det_level);
196 update_dom('POSTAL_CODE' , comp_contacts_rec.post_postal_code , g_company_add_det_level);
197 update_dom('PROVINCE' , comp_contacts_rec.post_province , g_company_add_det_level);
198
199 update_dom('PHYSICAL_ADDRESS', null, g_company_add_level);
200 update_dom('ADDRESS_LINE_1' , comp_contacts_rec.phy_add_line_1 , g_company_add_det_level);
201 update_dom('ADDRESS_LINE_2' , comp_contacts_rec.phy_add_line_2 , g_company_add_det_level);
202 update_dom('ADDRESS_LINE_3' , comp_contacts_rec.phy_add_line_3 , g_company_add_det_level);
203 update_dom('TOWN_CITY' , comp_contacts_rec.phy_town_or_city, g_company_add_det_level);
204 update_dom('POSTAL_CODE' , comp_contacts_rec.phy_postal_code , g_company_add_det_level);
205 update_dom('PROVINCE' , comp_contacts_rec.phy_province , g_company_add_det_level);
206
207 update_dom('TEL' , comp_contacts_rec.tel_no , g_company_det_level);
208 update_dom('FAX' , comp_contacts_rec.fax_no , g_company_det_level);
209 update_dom('E-MAIL' ,comp_contacts_rec.email_add, g_company_det_level);
210
211 -- Update WSP SDF DETAILS
212 open csr_comp_sdf_det;
213 fetch csr_comp_sdf_det into comp_sdf_det_rec;
214 close csr_comp_sdf_det;
215
216 update_dom('SDF_NAME', comp_sdf_det_rec.sdf_name, g_company_det_level);
217
218 update_dom('SDF_ADDRESS', null, g_company_add_level);
219 update_dom('ADDRESS_LINE_1' , comp_sdf_det_rec.sdf_add_line_1 , g_company_add_det_level);
220 update_dom('ADDRESS_LINE_2' , comp_sdf_det_rec.sdf_add_line_2 , g_company_add_det_level);
221 update_dom('ADDRESS_LINE_3' , comp_sdf_det_rec.sdf_add_line_3 , g_company_add_det_level);
222 update_dom('TOWN_CITY' , comp_sdf_det_rec.sdf_town_or_city, g_company_add_det_level);
223 update_dom('POSTAL_CODE' , comp_sdf_det_rec.sdf_postal_code , g_company_add_det_level);
224 update_dom('PROVINCE' , comp_sdf_det_rec.sdf_province , g_company_add_det_level);
225
226 update_dom('SDF_TEL' , comp_sdf_det_rec.sdf_tel , g_company_det_level);
227 update_dom('SDF_MOBILE', comp_sdf_det_rec.sdf_mobile, g_company_det_level);
228 update_dom('SDF_FAX' , comp_sdf_det_rec.sdf_fax , g_company_det_level);
229 update_dom('SDF_EMAIL' , comp_sdf_det_rec.sdf_email , g_company_det_level);
230 --
231 hr_utility.set_location('Leaving ' || l_proc, 10);
232
233 end update_company_data;
234 --
235 /*--------------------------------------------------------------------------
236 Name : update_wsp_data
237 Purpose : Updates WSP(Training and Beneficiary) data to XML DOM.
238 Assumes g_xml_dom is already initialized.
239 Arguments :
240 --------------------------------------------------------------------------*/
241 procedure update_wsp_data( p_legal_entity_id in number
242 , p_payroll_action_id in varchar2) is
243
244 l_proc varchar2(100) := g_package || 'update_wsp_data';
245
246 cursor csr_wsp_trng is
247 select action_information3 sk_num -- Skills priority number (i.e serial number)
248 , action_information4 training_priority -- Skills priority ( Education/ Training priority)
249 , action_information5 lev_1
250 , action_information6 lev_2
251 , action_information7 lev_3
252 , action_information8 lev_4
253 , action_information9 lev_5
254 , action_information10 lev_6
255 , action_information11 lev_7
256 , action_information12 lev_8
257 , action_information13 unknown
258 , action_information14 saqa_yes
259 , action_information15 saqa_no
260 , action_information16 saqa_id
261 , action_information17 year
262 from pay_action_information
263 where action_context_id = p_payroll_action_id
264 and action_information_category = 'ZA WSP TRAINING PROGRAMS'
265 and action_information2 = p_legal_entity_id
266 and action_context_type = 'PA'
267 order by sk_num;
268
269 -- changed action_context_type from PA to AAP
270 cursor csr_wsp_occ is
271 select pai.action_information6 occ_cat
272 from pay_payroll_actions ppa
273 , pay_assignment_actions paa
274 , pay_action_information pai
275 where ppa.payroll_action_id = p_payroll_action_id
276 AND paa.payroll_action_id = ppa.payroll_action_id
277 AND pai.action_context_id = paa.assignment_action_id
278 and pai.action_information_category = 'ZA WSP PERSON DETAILS'
279 and pai.action_information2 = p_legal_entity_id
280 and pai.action_context_type = 'AAP'
281 group by pai.action_information6;
282
283 cursor csr_wsp_occ_recs( p_occ_cat varchar) is
284 select distinct pai.action_information3 person_id
285 , pai.action_information4 race
286 , pai.action_information5 sex
287 , pai.action_information7 disability
288 from pay_payroll_actions ppa
289 , pay_assignment_actions paa
290 , pay_action_information pai
291 where ppa.payroll_action_id = p_payroll_action_id
292 AND paa.payroll_action_id = ppa.payroll_action_id
293 AND pai.action_context_id = paa.assignment_action_id
294 and pai.action_information_category = 'ZA WSP PERSON DETAILS'
295 and pai.action_information2 = p_legal_entity_id
296 and pai.action_context_type = 'AAP'
297 and pai.action_information6 = p_occ_cat;
298
299 cursor csr_wsp_occ_sk_prs( p_occ_cat varchar) is
300 select distinct pai.action_information11 sk_pr_num
301 from pay_payroll_actions ppa
302 , pay_assignment_actions paa
303 , pay_action_information pai
304 where ppa.payroll_action_id = p_payroll_action_id
305 AND paa.payroll_action_id = ppa.payroll_action_id
306 AND pai.action_context_id = paa.assignment_action_id
307 and pai.action_information_category = 'ZA WSP PERSON DETAILS'
308 and pai.action_information2 = p_legal_entity_id
309 and pai.action_context_type = 'AAP'
310 and pai.action_information6 = p_occ_cat
311 order by sk_pr_num;
312
313
314 l_ma number(15);
315 l_fa number(15);
316 l_da number(15);
317
318 l_mc number(15);
319 l_fc number(15);
320 l_dc number(15);
321
322 l_mi number(15);
323 l_fi number(15);
324 l_di number(15);
325
326 l_mw number(15);
327 l_fw number(15);
328 l_dw number(15);
329
330 l_mt number(15);
331 l_ft number(15);
332 l_dt number(15);
333 --For Grand Totals
334 l_wsp_ma_sum number(15) := 0;
335 l_wsp_fa_sum number(15) := 0;
336 l_wsp_da_sum number(15) := 0;
337
338 l_wsp_mc_sum number(15) := 0;
339 l_wsp_fc_sum number(15) := 0;
340 l_wsp_dc_sum number(15) := 0;
341
342 l_wsp_mi_sum number(15) := 0;
343 l_wsp_fi_sum number(15) := 0;
344 l_wsp_di_sum number(15) := 0;
345
346 l_wsp_mw_sum number(15) := 0;
347 l_wsp_fw_sum number(15) := 0;
348 l_wsp_dw_sum number(15) := 0;
349
350 l_wsp_mt_sum number(15) := 0;
351 l_wsp_ft_sum number(15) := 0;
352 l_wsp_dt_sum number(15) := 0;
353
354 l_wsp_end_year number(4);
355 l_leg_parameters varchar2(2000);
356
357 l_sk_pr_num varchar2(2000);
358 /*
359 01 Indian
360 02 African
361 03 Coloured
362 04 White
363 */
364
365 begin
366 --hr_utility.trace_on(null,'ZAWSPG');
367 hr_utility.set_location ('Entering ' || l_proc, 10);
368
369 -- WSP year
370 select legislative_parameters
371 into l_leg_parameters
372 from pay_payroll_actions
373 where payroll_action_id = p_payroll_action_id;
374 --
375 l_wsp_end_year := to_number(per_za_wsp_archive_pkg.get_parameter('PLAN_YEAR',l_leg_parameters));
376
377 hr_utility.set_location ('l_wsp_end_year :'||l_wsp_end_year,10);
378
379 if l_wsp_end_year is null then
380 l_wsp_end_year := 4712;
381 end if;
382
383 -- Create parent node for WSP data
384 update_dom('WSP', null, g_wsp_level);
385 hr_utility.set_location ('WSP :',10);
386 update_dom('WSP_START_YEAR', l_wsp_end_year - 1, g_wsp_date_level);
387 update_dom('WSP_END_YEAR', l_wsp_end_year, g_wsp_date_level);
388
389 -- Update WSP Training Details
390 for wsp_trng_rec in csr_wsp_trng
391 loop
392 update_dom('TRAINING_DETAILS', null, g_wsp_trng_level);
393 update_dom('SK_NUM' , wsp_trng_rec.sk_num , g_wsp_trng_det_level);
394 update_dom('TRAINING_PRIORITY', wsp_trng_rec.training_priority, g_wsp_trng_det_level);
395 update_dom('LEV_1' , wsp_trng_rec.lev_1 , g_wsp_trng_det_level);
396 update_dom('LEV_2' , wsp_trng_rec.lev_2 , g_wsp_trng_det_level);
397 update_dom('LEV_3' , wsp_trng_rec.lev_3 , g_wsp_trng_det_level);
398 update_dom('LEV_4' , wsp_trng_rec.lev_4 , g_wsp_trng_det_level);
399 update_dom('LEV_5' , wsp_trng_rec.lev_5 , g_wsp_trng_det_level);
400 update_dom('LEV_6' , wsp_trng_rec.lev_6 , g_wsp_trng_det_level);
401 update_dom('LEV_7' , wsp_trng_rec.lev_7 , g_wsp_trng_det_level);
402 update_dom('LEV_8' , wsp_trng_rec.lev_8 , g_wsp_trng_det_level);
403 update_dom('UNKNOWN' , wsp_trng_rec.unknown , g_wsp_trng_det_level);
404 update_dom('SAQA_YES' , wsp_trng_rec.saqa_yes , g_wsp_trng_det_level);
405 update_dom('SAQA_NO' , wsp_trng_rec.saqa_no , g_wsp_trng_det_level);
406 update_dom('SAQA_ID' , wsp_trng_rec.saqa_id , g_wsp_trng_det_level);
407 end loop;
408
409 -- Update WSP Beneficiary details
410 for wsp_occ_rec in csr_wsp_occ
411 loop
412 l_ma := 0; l_fa := 0; l_da := 0;
413 l_mc := 0; l_fc := 0; l_dc := 0;
414 l_mi := 0; l_fi := 0; l_di := 0;
415 l_mw := 0; l_fw := 0; l_dw := 0;
416 l_mt := 0; l_ft := 0; l_dt := 0;
417
418 l_sk_pr_num := '';
419
420 for wsp_sk_prs in csr_wsp_occ_sk_prs(wsp_occ_rec.occ_cat)
421 loop
422 l_sk_pr_num := l_sk_pr_num || ', ' || wsp_sk_prs.sk_pr_num;
423 end loop;
424
425 l_sk_pr_num := substr(l_sk_pr_num, 3);
426
427 for wsp_per_rec in csr_wsp_occ_recs(wsp_occ_rec.occ_cat)
428 loop
429 if wsp_per_rec.race = '02' --African
430 then
431 if wsp_per_rec.sex = 'M'
432 then
433 l_ma := l_ma + 1;
434 else
435 l_fa := l_fa + 1;
436 end if;
437
438 if wsp_per_rec.disability = 'Y'
439 then
440 l_da := l_da + 1;
441 end if;
442
443 elsif wsp_per_rec.race = '03' --Coloured
444 then
445 if wsp_per_rec.sex = 'M'
446 then
447 l_mc := l_mc + 1;
448 else
449 l_fc := l_fc + 1;
450 end if;
451
452 if wsp_per_rec.disability = 'Y'
453 then
454 l_dc := l_dc + 1;
455 end if;
456
457 elsif wsp_per_rec.race = '01' --Indian
458 then
459 if wsp_per_rec.sex = 'M'
460 then
461 l_mi := l_mi + 1;
462 else
463 l_fi := l_fi + 1;
464 end if;
465
466 if wsp_per_rec.disability = 'Y'
467 then
468 l_di := l_di + 1;
469 end if;
470
471 elsif wsp_per_rec.race = '04' --White
472 then
473 if wsp_per_rec.sex = 'M'
474 then
475 l_mw := l_mw + 1;
476 else
477 l_fw := l_fw + 1;
478 end if;
479
480 if wsp_per_rec.disability = 'Y'
481 then
482 l_dw := l_dw + 1;
483 end if;
484 end if;
485 end loop;
486
487 l_mt := l_ma + l_mc + l_mi + l_mw;
488 l_ft := l_fa + l_fc + l_fi + l_fw;
489 l_dt := l_da + l_dc + l_di + l_dw;
490
491 l_wsp_ma_sum := l_wsp_ma_sum + l_ma;
492 l_wsp_fa_sum := l_wsp_fa_sum + l_fa;
493 l_wsp_da_sum := l_wsp_da_sum + l_da;
494
495 l_wsp_mc_sum := l_wsp_mc_sum + l_mc;
496 l_wsp_fc_sum := l_wsp_fc_sum + l_fc;
497 l_wsp_dc_sum := l_wsp_dc_sum + l_dc;
498
499 l_wsp_mi_sum := l_wsp_mi_sum + l_mi;
500 l_wsp_fi_sum := l_wsp_fi_sum + l_fi;
501 l_wsp_di_sum := l_wsp_di_sum + l_di;
502
503 l_wsp_mw_sum := l_wsp_mw_sum + l_mw;
504 l_wsp_fw_sum := l_wsp_fw_sum + l_fw;
505 l_wsp_dw_sum := l_wsp_dw_sum + l_dw;
506
507 l_wsp_mt_sum := l_wsp_mt_sum + l_mt;
508 l_wsp_ft_sum := l_wsp_ft_sum + l_ft;
509 l_wsp_dt_sum := l_wsp_dt_sum + l_dt;
510
511
512 -- Update Beneficiary details to xml dom
513 update_dom('BENEFICIARIRY_DETAILS', null, g_wsp_benf_level);
514
515 update_dom('OCCUPATION' , wsp_occ_rec.occ_cat, g_wsp_benf_det_level);
516 update_dom('PRIORITY_NUMBER' , l_sk_pr_num , g_wsp_benf_det_level);
517
518 update_dom('MALE_AFRICANS' , l_ma, g_wsp_benf_det_level);
519 update_dom('FEMALE_AFRICANS' , l_fa, g_wsp_benf_det_level);
520 update_dom('DISABILED_AFRICANS', l_da, g_wsp_benf_det_level);
521
522 update_dom('MALE_COLOUREDS' , l_mc, g_wsp_benf_det_level);
523 update_dom('FEMALE_COLOUREDS' , l_fc, g_wsp_benf_det_level);
524 update_dom('DISABILED_COLOUREDS', l_dc, g_wsp_benf_det_level);
525
526 update_dom('MALE_INDIANS' , l_mi, g_wsp_benf_det_level);
527 update_dom('FEMALE_INDIANS' , l_fi, g_wsp_benf_det_level);
528 update_dom('DISABILED_INDIANS', l_di, g_wsp_benf_det_level);
529
530 update_dom('MALE_WHITES' , l_mw, g_wsp_benf_det_level);
531 update_dom('FEMALE_WHITES' , l_fw, g_wsp_benf_det_level);
532 update_dom('DISABILED_WHITES', l_dw, g_wsp_benf_det_level);
533
534 update_dom('MALE_TOTALS' , l_mt, g_wsp_benf_det_level);
535 update_dom('FEMALE_TOTALS' , l_ft, g_wsp_benf_det_level);
536 update_dom('DISABILED_TOTALS', l_dt, g_wsp_benf_det_level);
537 end loop;
538 --
539 hr_utility.set_location ('MALE_AFRICANS_TOT :',10);
540 update_dom('MALE_AFRICANS_TOT' , l_wsp_ma_sum, g_wsp_benf_level);
541 hr_utility.set_location ('MALE_AFRICANS_TOT :',20);
542 update_dom('FEMALE_AFRICANS_TOT' , l_wsp_fa_sum, g_wsp_benf_level);
543 update_dom('DISABILED_AFRICANS_TOT' , l_wsp_da_sum, g_wsp_benf_level);
544
545 update_dom('MALE_COLOUREDS_TOT' , l_wsp_mc_sum, g_wsp_benf_level);
546 update_dom('FEMALE_COLOUREDS_TOT' , l_wsp_fc_sum, g_wsp_benf_level);
547 update_dom('DISABILED_COLOUREDS_TOT', l_wsp_dc_sum, g_wsp_benf_level);
548
549 update_dom('MALE_INDIANS_TOT' , l_wsp_mi_sum, g_wsp_benf_level);
550 update_dom('FEMALE_INDIANS_TOT' , l_wsp_fi_sum, g_wsp_benf_level);
551 update_dom('DISABILED_INDIANS_TOT' , l_wsp_di_sum, g_wsp_benf_level);
552
553 update_dom('MALE_WHITES_TOT' , l_wsp_mw_sum, g_wsp_benf_level);
554 update_dom('FEMALE_WHITES_TOT' , l_wsp_fw_sum, g_wsp_benf_level);
555 update_dom('DISABILED_WHITES_TOT' , l_wsp_dw_sum, g_wsp_benf_level);
556
557 update_dom('MALE_TOTALS_TOT' , l_wsp_mt_sum, g_wsp_benf_level);
558 update_dom('FEMALE_TOTALS_TOT' , l_wsp_ft_sum, g_wsp_benf_level);
559 update_dom('DISABILED_TOTALS_TOT' , l_wsp_dt_sum, g_wsp_benf_level);
560
561
562 hr_utility.set_location('Leaving ' || l_proc, 10);
563 -- hr_utility.trace_off;
564 end update_wsp_data;
565 --
566
567 /*--------------------------------------------------------------------------
568 Name : update_atr_data
569 Purpose : Updates ATR(Training and Beneficiary) data to XML DOM.
570 Assumes g_xml_dom is already initialized.
571 Arguments :
572 --------------------------------------------------------------------------*/
573 procedure update_atr_data( p_legal_entity_id in number
574 , p_payroll_action_id in varchar2) is
575
576 l_proc varchar2(100) := g_package || 'update_atr_data';
577
578 cursor csr_atr_trng is
579 select action_information3 sk_num -- Skills priority number (i.e serial number)
580 , action_information4 training_priority -- Skills priority ( Education/ Training priority)
581 , action_information5 lev_1
582 , action_information6 lev_2
583 , action_information7 lev_3
584 , action_information8 lev_4
585 , action_information9 lev_5
586 , action_information10 lev_6
587 , action_information11 lev_7
588 , action_information12 lev_8
589 , action_information13 unknown
590 , action_information14 saqa_yes
591 , action_information15 saqa_no
592 , action_information16 saqa_id
593 , action_information17 year
594 from pay_action_information
595 where action_context_id = p_payroll_action_id
596 and action_information_category = 'ZA ATR TRAINING PROGRAMS'
597 and action_information2 = p_legal_entity_id
598 and action_context_type = 'PA'
599 order by sk_num;
600
601 cursor csr_atr_occ is
602 select action_information6 occ_cat
603 from pay_payroll_actions ppa
604 , pay_assignment_actions paa
605 , pay_action_information pai
606 where ppa.payroll_action_id = p_payroll_action_id
607 AND paa.payroll_action_id = ppa.payroll_action_id
608 AND pai.action_context_id = paa.assignment_action_id
609 and pai.action_information_category = 'ZA ATR PERSON DETAILS'
610 and pai.action_information2 = p_legal_entity_id
611 and pai.action_context_type = 'AAP'
612 group by pai.action_information6;
613
614 cursor csr_atr_occ_recs( p_occ_cat varchar) is
615 select distinct pai.action_information3 person_id
616 , action_information4 race
617 , action_information5 sex
618 , action_information7 disability
619 , action_information14 status
620 from pay_payroll_actions ppa
621 , pay_assignment_actions paa
622 , pay_action_information pai
623 where ppa.payroll_action_id = p_payroll_action_id
624 AND paa.payroll_action_id = ppa.payroll_action_id
625 AND pai.action_context_id = paa.assignment_action_id
626 and pai.action_information_category = 'ZA ATR PERSON DETAILS'
627 and pai.action_information2 = p_legal_entity_id
628 and pai.action_context_type = 'AAP'
629 and pai.action_information6 = p_occ_cat;
630
631 cursor csr_atr_occ_sk_prs( p_occ_cat varchar) is
632 select distinct action_information11 sk_pr_num
633 from pay_payroll_actions ppa
634 , pay_assignment_actions paa
635 , pay_action_information pai
636 where ppa.payroll_action_id = p_payroll_action_id
637 AND paa.payroll_action_id = ppa.payroll_action_id
638 AND pai.action_context_id = paa.assignment_action_id
639 and pai.action_information_category = 'ZA ATR PERSON DETAILS'
640 and pai.action_information2 = p_legal_entity_id
641 and pai.action_context_type = 'AAP'
642 and pai.action_information6 = p_occ_cat
643 order by sk_pr_num;
644
645 -- For Attended
646 l_ma number(15);
647 l_fa number(15);
648 l_da number(15);
649
650 l_mc number(15);
651 l_fc number(15);
652 l_dc number(15);
653
654 l_mi number(15);
655 l_fi number(15);
656 l_di number(15);
657
658 l_mw number(15);
659 l_fw number(15);
660 l_dw number(15);
661
662 l_mt number(15);
663 l_ft number(15);
664 l_dt number(15);
665
666 -- For Completed
667 l_cma number(15);
668 l_cfa number(15);
669 l_cda number(15);
670
671 l_cmc number(15);
672 l_cfc number(15);
673 l_cdc number(15);
674
675 l_cmi number(15);
676 l_cfi number(15);
677 l_cdi number(15);
678
679 l_cmw number(15);
680 l_cfw number(15);
681 l_cdw number(15);
682
683 l_cmt number(15);
684 l_cft number(15);
685 l_cdt number(15);
686 --For Grand Totals
687 l_ma_sum number(15) := 0;
688 l_fa_sum number(15) := 0;
689 l_da_sum number(15) := 0;
690
691 l_mc_sum number(15) := 0;
692 l_fc_sum number(15) := 0;
693 l_dc_sum number(15) := 0;
694
695 l_mi_sum number(15) := 0;
696 l_fi_sum number(15) := 0;
697 l_di_sum number(15) := 0;
698
699 l_mw_sum number(15) := 0;
700 l_fw_sum number(15) := 0;
701 l_dw_sum number(15) := 0;
702
703 l_mt_sum number(15) := 0;
704 l_ft_sum number(15) := 0;
705 l_dt_sum number(15) := 0;
706
707
708 l_sk_pr_num varchar2(2000);
709
710 begin
711 hr_utility.set_location ('Entering ' || l_proc, 10);
712
713
714 -- Create parent node for ATR data
715 update_dom('ATR', null, g_atr_level);
716
717 -- Update ATR Training Details
718 for atr_trng_rec in csr_atr_trng
719 loop
720 update_dom('TRAINING_DETAILS', null, g_atr_trng_level);
721
722 update_dom('SK_NUM' , atr_trng_rec.sk_num , g_atr_trng_det_level);
723 update_dom('TRAINING_PRIORITY', atr_trng_rec.training_priority, g_atr_trng_det_level);
724 update_dom('LEV_1' , atr_trng_rec.lev_1 , g_atr_trng_det_level);
725 update_dom('LEV_2' , atr_trng_rec.lev_2 , g_atr_trng_det_level);
726 update_dom('LEV_3' , atr_trng_rec.lev_3 , g_atr_trng_det_level);
727 update_dom('LEV_4' , atr_trng_rec.lev_4 , g_atr_trng_det_level);
728 update_dom('LEV_5' , atr_trng_rec.lev_5 , g_atr_trng_det_level);
729 update_dom('LEV_6' , atr_trng_rec.lev_6 , g_atr_trng_det_level);
730 update_dom('LEV_7' , atr_trng_rec.lev_7 , g_atr_trng_det_level);
731 update_dom('LEV_8' , atr_trng_rec.lev_8 , g_atr_trng_det_level);
732 update_dom('UNKNOWN' , atr_trng_rec.unknown , g_atr_trng_det_level);
733 update_dom('SAQA_YES' , atr_trng_rec.saqa_yes , g_atr_trng_det_level);
734 update_dom('SAQA_NO' , atr_trng_rec.saqa_no , g_atr_trng_det_level);
735 update_dom('SAQA_ID' , atr_trng_rec.saqa_id , g_atr_trng_det_level);
736 end loop;
737
738 -- Update ATR Beneficiary details
739 for atr_occ_rec in csr_atr_occ
740 loop
741 l_ma := 0; l_fa := 0; l_da := 0;
742 l_mc := 0; l_fc := 0; l_dc := 0;
743 l_mi := 0; l_fi := 0; l_di := 0;
744 l_mw := 0; l_fw := 0; l_dw := 0;
745 l_mt := 0; l_ft := 0; l_dt := 0;
746
747 l_cma := 0; l_cfa := 0; l_cda := 0;
748 l_cmc := 0; l_cfc := 0; l_cdc := 0;
749 l_cmi := 0; l_cfi := 0; l_cdi := 0;
750 l_cmw := 0; l_cfw := 0; l_cdw := 0;
751 l_cmt := 0; l_cft := 0; l_cdt := 0;
752
753 l_sk_pr_num := '';
754
755 for atr_sk_prs in csr_atr_occ_sk_prs(atr_occ_rec.occ_cat)
756 loop
757 l_sk_pr_num := l_sk_pr_num || ', ' || atr_sk_prs.sk_pr_num;
758 end loop;
759
760 l_sk_pr_num := substr(l_sk_pr_num, 3);
761
762 for atr_per_rec in csr_atr_occ_recs(atr_occ_rec.occ_cat)
763 loop
764 if atr_per_rec.race = '02' --African
765 then
766 if atr_per_rec.sex = 'M'
767 then
768 if atr_per_rec.status = 'ATTENDED'
769 then
770 l_ma := l_ma + 1;
771 else
772 l_cma := l_cma + 1;
773 end if;
774 else
775 if atr_per_rec.status = 'ATTENDED'
776 then
777 l_fa := l_fa + 1;
778 else
779 l_cfa := l_cfa + 1;
780 end if;
781 end if;
782
783 if atr_per_rec.disability = 'Y'
784 then
785 if atr_per_rec.status = 'ATTENDED'
786 then
787 l_da := l_da + 1;
788 else
789 l_cda := l_cda + 1;
790 end if;
791 end if;
792
793 elsif atr_per_rec.race = '03' -- Coloured
794 then
795 if atr_per_rec.sex = 'M'
796 then
797 if atr_per_rec.status = 'ATTENDED'
798 then
799 l_mc := l_mc + 1;
800 else
801 l_cmc := l_cmc + 1;
802 end if;
803 else
804 if atr_per_rec.status = 'ATTENDED'
805 then
806 l_fc := l_fc + 1;
807 else
808 l_cfc := l_cfc + 1;
809 end if;
810 end if;
811
812 if atr_per_rec.disability = 'Y'
813 then
814 if atr_per_rec.status = 'ATTENDED'
815 then
816 l_dc := l_dc + 1;
817 else
818 l_cdc := l_cdc + 1;
819 end if;
820 end if;
821
822 elsif atr_per_rec.race = '01' -- Indian
823 then
824 if atr_per_rec.sex = 'M'
825 then
826 if atr_per_rec.status = 'ATTENDED'
827 then
828 l_mi := l_mi + 1;
829 else
830 l_cmi := l_cmi + 1;
831 end if;
832 else
833 if atr_per_rec.status = 'ATTENDED'
834 then
835 l_fi := l_fi + 1;
836 else
837 l_cfi := l_cfi + 1;
838 end if;
839 end if;
840
841 if atr_per_rec.disability = 'Y'
842 then
843 if atr_per_rec.status = 'ATTENDED'
844 then
845 l_di := l_di + 1;
846 else
847 l_cdi := l_cdi + 1;
848 end if;
849 end if;
850
851 elsif atr_per_rec.race = '04' --White
852 then
853 if atr_per_rec.sex = 'M'
854 then
855 if atr_per_rec.status = 'ATTENDED'
856 then
857 l_mw := l_mw + 1;
858 else
859 l_cmw := l_cmw + 1;
860 end if;
861 else
862 if atr_per_rec.status = 'ATTENDED'
863 then
864 l_fw := l_fw + 1;
865 else
866 l_cfw := l_cfw + 1;
867 end if;
868 end if;
869
870 if atr_per_rec.disability = 'Y'
871 then
872 if atr_per_rec.status = 'ATTENDED'
873 then
874 l_dw := l_dw + 1;
875 else
876 l_cdw := l_cdw + 1;
877 end if;
878 end if;
879 end if;
880 end loop;
881
882 -- Attended Totals
883 l_mt := l_ma + l_mc + l_mi + l_mw;
884 l_ft := l_fa + l_fc + l_fi + l_fw;
885 l_dt := l_da + l_dc + l_di + l_dw;
886
887 -- Completed Totals
888 l_cmt := l_cma + l_cmc + l_cmi + l_cmw;
889 l_cft := l_cfa + l_cfc + l_cfi + l_cfw;
890 l_cdt := l_cda + l_cdc + l_cdi + l_cdw;
891
892 --For Grand Totals
893 l_ma_sum := l_ma_sum + l_ma + l_cma;
894 l_fa_sum := l_fa_sum + l_fa + l_cfa;
895 l_da_sum := l_da_sum + l_da + l_cda;
896
897 l_mc_sum := l_mc_sum + l_mc + l_cmc;
898 l_fc_sum := l_fc_sum + l_fc + l_cfc;
899 l_dc_sum := l_dc_sum + l_dc + l_cdc;
900
901 l_mi_sum := l_mi_sum + l_mi + l_cmi;
902 l_fi_sum := l_fi_sum + l_fi + l_cfi;
903 l_di_sum := l_di_sum + l_di + l_cdi;
904
905 l_mw_sum := l_mw_sum + l_mw + l_cmw;
906 l_fw_sum := l_fw_sum + l_fw + l_cfw;
907 l_dw_sum := l_dw_sum + l_dw + l_cdw;
908
909 l_mt_sum := l_mt_sum + l_mt + l_cmt;
910 l_ft_sum := l_ft_sum + l_ft + l_cft;
911 l_dt_sum := l_dt_sum + l_dt + l_cdt;
912
913
914 -- Update Beneficiary details to xml dom
915 update_dom('BENEFICIARIRY_DETAILS', null, g_atr_benf_level);
916
917 update_dom('OCCUPATION' , atr_occ_rec.occ_cat, g_atr_benf_det_level);
918 update_dom('PRIORITY_NUMBER' , l_sk_pr_num , g_atr_benf_det_level);
919
920 -- Attended person details
921 update_dom('MALE_AFRICANS' , l_ma, g_atr_benf_det_level);
922 update_dom('FEMALE_AFRICANS' , l_fa, g_atr_benf_det_level);
923 update_dom('DISABILED_AFRICANS', l_da, g_atr_benf_det_level);
924
925 update_dom('MALE_COLOUREDS' , l_mc, g_atr_benf_det_level);
926 update_dom('FEMALE_COLOUREDS' , l_fc, g_atr_benf_det_level);
927 update_dom('DISABILED_COLOUREDS', l_dc, g_atr_benf_det_level);
928
929 update_dom('MALE_INDIANS' , l_mi, g_atr_benf_det_level);
930 update_dom('FEMALE_INDIANS' , l_fi, g_atr_benf_det_level);
931 update_dom('DISABILED_INDIANS', l_di, g_atr_benf_det_level);
932
933 update_dom('MALE_WHITES' , l_mw, g_atr_benf_det_level);
934 update_dom('FEMALE_WHITES' , l_fw, g_atr_benf_det_level);
935 update_dom('DISABILED_WHITES', l_dw, g_atr_benf_det_level);
936
937 update_dom('MALE_TOTALS' , l_mt, g_atr_benf_det_level);
938 update_dom('FEMALE_TOTALS' , l_ft, g_atr_benf_det_level);
939 update_dom('DISABILED_TOTALS', l_dt, g_atr_benf_det_level);
940
941 -- Completed person details
942 update_dom('MALE_AFRICANS_COMP' , l_cma, g_atr_benf_det_level);
943 update_dom('FEMALE_AFRICANS_COMP' , l_cfa, g_atr_benf_det_level);
944 update_dom('DISABILED_AFRICANS_COMP', l_cda, g_atr_benf_det_level);
945
946 update_dom('MALE_COLOUREDS_COMP' , l_cmc, g_atr_benf_det_level);
947 update_dom('FEMALE_COLOUREDS_COMP' , l_cfc, g_atr_benf_det_level);
948 update_dom('DISABILED_COLOUREDS_COMP', l_cdc, g_atr_benf_det_level);
949
950 update_dom('MALE_INDIANS_COMP' , l_cmi, g_atr_benf_det_level);
951 update_dom('FEMALE_INDIANS_COMP' , l_cfi, g_atr_benf_det_level);
952 update_dom('DISABILED_INDIANS_COMP', l_cdi, g_atr_benf_det_level);
953
954 update_dom('MALE_WHITES_COMP' , l_cmw, g_atr_benf_det_level);
955 update_dom('FEMALE_WHITES_COMP' , l_cfw, g_atr_benf_det_level);
956 update_dom('DISABILED_WHITES_COMP', l_cdw, g_atr_benf_det_level);
957
958 update_dom('MALE_TOTALS_COMP' , l_cmt, g_atr_benf_det_level);
959 update_dom('FEMALE_TOTALS_COMP' , l_cft, g_atr_benf_det_level);
960 update_dom('DISABILED_TOTALS_COMP', l_cdt, g_atr_benf_det_level);
961
962 end loop;
963 -- for grand totals
964 update_dom('MALE_AFRICANS_TOT' , l_ma_sum, g_atr_benf_level);
965 update_dom('FEMALE_AFRICANS_TOT' , l_fa_sum, g_atr_benf_level);
966 update_dom('DISABILED_AFRICANS_TOT' , l_da_sum, g_atr_benf_level);
967
968 update_dom('MALE_COLOUREDS_TOT' , l_mc_sum, g_atr_benf_level);
969 update_dom('FEMALE_COLOUREDS_TOT' , l_fc_sum, g_atr_benf_level);
970 update_dom('DISABILED_COLOUREDS_TOT', l_dc_sum, g_atr_benf_level);
971
972 update_dom('MALE_INDIANS_TOT' , l_mi_sum, g_atr_benf_level);
973 update_dom('FEMALE_INDIANS_TOT' , l_fi_sum, g_atr_benf_level);
974 update_dom('DISABILED_INDIANS_TOT' , l_di_sum, g_atr_benf_level);
975
976 update_dom('MALE_WHITES_TOT' , l_mw_sum, g_atr_benf_level);
977 update_dom('FEMALE_WHITES_TOT' , l_fw_sum, g_atr_benf_level);
978 update_dom('DISABILED_WHITES_TOT' , l_dw_sum, g_atr_benf_level);
979
980 update_dom('MALE_TOTALS_TOT' , l_mt_sum, g_atr_benf_level);
981 update_dom('FEMALE_TOTALS_TOT' , l_ft_sum, g_atr_benf_level);
982 update_dom('DISABILED_TOTALS_TOT' , l_dt_sum, g_atr_benf_level);
983
984 hr_utility.set_location('Leaving ' || l_proc, 10);
985
986 end update_atr_data;
987 --
988
989 /*--------------------------------------------------------------------------
990 Name : populate_xml_data
991 Purpose : Populates XML data as CLOB for WSP report
992 Arguments : p_xml -> out variable
993 --------------------------------------------------------------------------*/
994 procedure populate_xml_data ( p_business_group_id in number
995 , p_payroll_action_id in varchar2
996 , p_legal_entity_id in number
997 , p_template_name IN VARCHAR2
998 , p_xml out nocopy clob) is
999
1000 l_proc varchar2(100) := g_package || 'populate_xml_data';
1001
1002 main_node xmldom.DOMNode;
1003 root_node xmldom.DOMNode;
1004 parent_node xmldom.DOMNode;
1005 item_node xmldom.DOMNode;
1006
1007 root_elmt xmldom.DOMElement;
1008 item_elmt xmldom.DOMElement;
1009 item_text xmldom.DOMText;
1010
1011 cursor csr_legal_entities is
1012 select distinct action_information2 legal_entity_id -- Legal entity ID
1013 from pay_action_information
1014 where action_context_id = p_payroll_action_id
1015 and action_information1 = p_business_group_id
1016 and action_information2 = nvl(p_legal_entity_id, action_information2)
1017 and action_information_category = 'ZA WSP EMPLOYER DETAILS'
1018 and action_context_type = 'PA';
1019
1020 begin
1021 -- hr_utility.trace_on(null, 'WSP_GEN');
1022 hr_utility.set_location ('Entering ' || l_proc, 10);
1023
1024 -- Initialize g_xml_dom and create root node
1025 g_xml_dom := xmldom.newDOMDocument;
1026 main_node := xmldom.makeNode(g_xml_dom);
1027 root_elmt := xmldom.createElement(g_xml_dom, 'ZA_WSP_ATR_ROOT');
1028 root_node := xmldom.appendChild(main_node, xmldom.makeNode(root_elmt));
1029 g_node_list(g_root_level) := root_node;
1030
1031 for rec in csr_legal_entities
1032 loop
1033 update_dom('ZA_WSP_ATR_DATA', null, g_root_level);
1034
1035 -- Update all company level data to g_xml_dom
1036 update_company_data(rec.legal_entity_id, p_payroll_action_id);
1037
1038 -- Update WSP Training and Beneficiary details
1039 update_wsp_data(rec.legal_entity_id, p_payroll_action_id);
1040
1041 -- Update ATR Training and Beneficiary details
1042 update_atr_data(rec.legal_entity_id, p_payroll_action_id);
1043 end loop;
1044
1045 -- Update out variable p_xml and release xml dom
1046 dbms_lob.createtemporary(p_xml, true);
1047 xmldom.writeToClob(g_xml_dom, p_xml);
1048 xmldom.freeDocument(g_xml_dom);
1049 -- open the file p_xml
1050 -- Why ? In the Core wrapper package(PAY_XML_GEN_PKG) this is being closed
1051 dbms_lob.open(p_xml, dbms_lob.lob_readonly);
1052
1053 hr_utility.set_location('Leaving ' || l_proc, 10);
1054
1055 -- hr_utility.trace_off;
1056
1057 end populate_xml_data;
1058
1059 end PER_ZA_WSP_XML_GEN_PKG;
1060