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